Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

The Complete SQL Server 2005 Permissions Report Expand / Collapse
Author
Message
Posted Monday, August 3, 2009 11:07 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 21, 2014 10:38 AM
Points: 1,036, Visits: 288
I have run this against SQL 2008 Enterprise x64 and it works the same as SQL 2005 Enterprise x64.
Post #764250
Posted Monday, August 3, 2009 11:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, August 21, 2013 7:09 PM
Points: 5, Visits: 22
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.
Post #764254
Posted Monday, August 3, 2009 11:31 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 10, 2011 2:04 PM
Points: 65, Visits: 67
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.
Post #764265
Posted Monday, August 3, 2009 12:46 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 10, 2011 2:04 PM
Points: 65, Visits: 67
The format problem is resolved. The script in the original post should work now.
Post #764306
Posted Thursday, January 28, 2010 10:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 11, 2010 1:58 AM
Points: 1, Visits: 5
Richard, what kind of use policy does this script concern?
Post #855447
Posted Monday, May 10, 2010 6:38 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 7:58 AM
Points: 6, 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
Post #918938
Posted Monday, May 10, 2010 11:24 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 8:43 AM
Points: 442, Visits: 620
Do you have an example of what output should look like ?

thanks
jim
Post #919204
Posted Monday, May 10, 2010 12:43 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, October 17, 2014 3:17 PM
Points: 30, Visits: 298
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?




Post #919252
Posted Friday, October 26, 2012 9:28 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:33 PM
Points: 2,389, Visits: 2,779
Thank you so much for this script. Is there any equivalent script compatible with SQL Server 2000?

Thanks for any help.

- webrunner


-------------------
"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
Post #1377679
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse