SQL Clone
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: 1104 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 (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 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
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 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
SSC-Enthusiastic
SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)SSC-Enthusiastic (113 reputation)

Group: General Forum Members
Points: 113 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 (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 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
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

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

thanks
jim
WHug
WHug
SSC Veteran
SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)SSC Veteran (237 reputation)

Group: General Forum Members
Points: 237 Visits: 412
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
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7537 Visits: 3997
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
SSCertifiable
SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)SSCertifiable (7.5K reputation)

Group: General Forum Members
Points: 7537 Visits: 3997
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