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.
Pingback: SQL login object permissions via PowerShell | SQL DBA with A Beard