http://www.sqlservercentral.com/blogs/adventuresinsql/2009/12/31/script-individual-user-rights-in-a-database-with-powershell/

Printed 2014/07/29 08:54AM

Script Individual User Rights in a Database with PowerShell

By David Levy, 2009/12/31

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.


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.