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

Get Public role permissions Expand / Collapse
Author
Message
Posted Wednesday, February 27, 2013 3:39 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:30 AM
Points: 403, Visits: 982
In SQL 2000, 2005, 2008, 2008r2
How to retrieve the permission for the default Public role in an db
Post #1424444
Posted Wednesday, February 27, 2013 4:06 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:15 AM
Points: 2,372, Visits: 7,562
2005/2008 it would be something like this: -
SELECT a.principal_id, a.name, OBJECT_NAME(b.major_id) AS [object], b.[permission_name], b.state_desc
FROM sys.database_principals a
LEFT OUTER JOIN sys.database_permissions b ON a.principal_id = b.grantee_principal_id
WHERE a.name = 'public';

You'd need to look up the mappings to 2000 to figure out an equivalent there --> http://msdn.microsoft.com/en-us/library/ms187997.aspx



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1424456
Posted Wednesday, February 27, 2013 4:27 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 9:30 AM
Points: 403, Visits: 982
Cadavre

Your query works. Is it the same as the one
select (major_id),OBJECT_NAME(major_id),grantee_principal_id,permission_name,state_desc from sys.database_permissions
where grantee_principal_id=0
Post #1424461
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse