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 $server = new-object Microsoft.SqlServer.Management.SMO.Server($serverConnection) |
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.
