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.
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:
$serverConnection = new-object Microsoft.SqlServer.Management.Common.ServerConnection
$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
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.