Introduction
Tonight on Twitter the call went out on #SqlHelp looking for a way to script just database permissions for a specific user. I wrote a script once that I was pretty sure did that so I jumped in to help. It turns out that I lost that particular script and it really did not do the right thing anyway. Somewhat embarassed I decided I better stay up and get a script written to solve the problem rather than admit to wasting someone’s time.
The Result
After a couple of hours of hacking through the various SMO classes I was able to cobble together the script below. I had forgetten how much I enjoyed challenges like this from my developer days so this was actually quite a treat.
Here is the code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | $serverName="localhost" $databaseName="AdventureWorks" $serverConnection = new-object Microsoft.SqlServer.Management.Common.ServerConnection $serverConnection.ServerInstance=$serverName $server = new-object Microsoft.SqlServer.Management.SMO.Server($serverConnection) $database = $server.Databases[$databaseName] foreach($user in $database.Users) { foreach($databasePermission in $database.EnumDatabasePermissions($user.Name)) { Write-Host $databasePermission.PermissionState $databasePermission.PermissionType "TO" $databasePermission.Grantee } foreach($objectPermission in $database.EnumObjectPermissions($user.Name)) { Write-Host $objectPermission.PermissionState $objectPermission.PermissionType "ON" $objectPermission.ObjectName "TO" $objectPermission.Grantee } } $server.ConnectionContext.Disconnect() |
Update
The discussion continues on this one. The current thinking is that you should be able to call User.Script(). Jonathan Kehayias (Blog, Twitter) will be logging a Connect item and I will post voting information here.
You can call $user.script($scriptoptions) here and it works great if your options include DatabaseRoleMemberships and if all your object level permissions are managed via database roles then you are set. It seems to be that the option of permissions does not go get you the object level permissions. That is a HUGE Pain. I will vote on this for sure if the item gets put on connect.
Pingback: SQL login object permissions via PowerShell | SQL DBA with A Beard