Trying to get a ServerPermissionSet for a login and apply it to a new login. Can't seem to grasp if there is a relationship between the ServerPermissionSet and a Server.Login, or if there is a better way to do this. I can do this with tSQL, but am trying to look at this from the SMO perspective. Here is some sudo code on what I am trying to accomplish.
$server = new-object ('Microsoft.SqlServer.Management.Smo.Server')
"my_server"
$sps = new-object -TypeName
Microsoft.SqlServer.Management.SMO.ServerPermissionSet
$newlogin = new-object ('Microsoft.SqlServer.Management.Smo.Login') $Server,
"new_login"
$oldlogin = $server.Logins["old_login"]
// THIS PART OBVIOUSLY DOESN'T WORK
$newlogin.Create("password")
$sps = $oldlogin.ServerPermissionSet
$server.Grant($sps,"new_login")
I'm using the code example here to inform my answer:
$server = new-object ('Microsoft.SqlServer.Management.Smo.Server')
"my_server"
$newlogin = new-object ('Microsoft.SqlServer.Management.Smo.Login') $Server,
"new_login"
$newlogin.Create("password")
$sps = $server.EnumServerPermissions("old_login")
$server.Grant($sps,"new_login")
That is, you have to ask the server to enumerate the server permissions for old_login which will generate a ServerPermissionSet[] which you can then pass to the Grant method on the server object.
But I do have to ask: is there a reason not to use a server-level role to encapsulate these permissions and just grant membership in the role to the new login?