Get Public role permissions

Question

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

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%5B/url%5D

Forever trying to learn
My blog - http://www.cadavre.co.uk/
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/

balasach82 SSCrazy Eights Points: 9653 More actions · Answer 2

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