Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


The Complete SQL Server 2005 Permissions Report


The Complete SQL Server 2005 Permissions Report

Author
Message
Rick Romack
Rick Romack
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1056 Visits: 297
I have run this against SQL 2008 Enterprise x64 and it works the same as SQL 2005 Enterprise x64.
lindsey.mundy
lindsey.mundy
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 23
The version contained in the link from jon.malenfant above works great (even on SQL 2008). I could not see any issues in the script, even with notepad, so thanks jon.
Richard Lu-422582
Richard Lu-422582
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 70
Sorry Guys... There was something wrong with the web editor. I have just resubmitted the script by removing all the  . But until the script gets published again here, you might have to DIY removing the   in the script, or use jon.malenfant's attachment http://www.sqlservercentral.com/Forums/Attachment3739.aspx. Thanks jon.
Richard Lu-422582
Richard Lu-422582
Valued Member
Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)Valued Member (65 reputation)

Group: General Forum Members
Points: 65 Visits: 70
The format problem is resolved. The script in the original post should work now.
tim.bosman
tim.bosman
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 5
Richard, what kind of use policy does this script concern?
Kyle Van Andel
Kyle Van Andel
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 202
Excellent script, thanks! I was not seeing the object level perms for users tied to database roles I had created, so I simply added another OR statement to the where clause to get what I was looking for:

SELECT DISTINCT Principal_Name,Login_Name, DatabaseName, ObjectName, ObjectType, PermissionName, state_desc, Grantor
FROM @ObjectPermissions op
WHERE ISNULL(Login_Name, '') like @loginName
OR ISNULL(Principal_Name, '') like @loginName
OR ISNULL(Principal_Name, '') in (SELECT DISTINCT DB_RoleMember
FROM @DBRolePermissions dbrp
WHERE (ISNULL(Login_Name, '') LIKE @loginName
OR ISNULL(Principal_Name, '') LIKE @loginName)
AND dbrp.DatabaseName = op.DatabaseName
AND Permission_Type = 'DATABASE_ROLE')
ORDER BY DatabaseName, Principal_Name, ObjectName, PermissionName
JC-3113
JC-3113
SSC-Addicted
SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)SSC-Addicted (450 reputation)

Group: General Forum Members
Points: 450 Visits: 620
Do you have an example of what output should look like ?

thanks
jim
WHug
WHug
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 394
Great script! I don't see synonyms, can that be added, and the "object level perms for users tied to database roles I had created" from above?



webrunner
webrunner
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3031 Visits: 3753
Thank you so much for this script. Is there any equivalent script compatible with SQL Server 2000?

Thanks for any help.

- webrunner

-------------------
"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.
Ref.: http://www.adminarsenal.com/admin-arsenal-blog/powershell-how-to-write-your-first-powershell-script

"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
webrunner
webrunner
Hall of Fame
Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)Hall of Fame (3K reputation)

Group: General Forum Members
Points: 3031 Visits: 3753
webrunner (10/26/2012)
Thank you so much for this script. Is there any equivalent script compatible with SQL Server 2000?

Thanks for any help.

- webrunner


Just want to add an updated note that I am using still this script well past SQL 2000 :-)

Thanks again - this script is so useful!

- webrunner

-------------------
"I love spending twice as long and working twice as hard to get half as much done!" – Nobody ever.
Ref.: http://www.adminarsenal.com/admin-arsenal-blog/powershell-how-to-write-your-first-powershell-script

"Operator! Give me the number for 911!" - Homer Simpson

"A SQL query walks into a bar and sees two tables. He walks up to them and says 'Can I join you?'"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search