Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 User permissions

Author  Topic 

Peter99
Constraint Violating Yak Guru

498 Posts

Posted - 2013-06-10 : 17:10:25
Hi,

Is there any query that we can run to get user permissions on database, table, view, sp level in a given database?

Thanks

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-10 : 17:40:03
See if one of these functions will do what you want:
http://msdn.microsoft.com/en-us/library/ms189802.aspx
http://msdn.microsoft.com/en-us/library/ms176097.aspx
Go to Top of Page

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-06-18 : 15:29:09
I found this a while back:
SELECT PER.class_desc                               AS PermClass
,PER.[type] AS PermType
,ISNULL(SCH.name + N'.' + OBJ.name,DB_NAME()) AS ObjectName
,ISNULL(COL.Name, N'') AS ColumnName
,PRC.name AS PrincName
,PRC.type_desc AS PrincType
,GRT.name AS GrantorName
,PER.permission_name AS PermName
,PER.state_desc AS PermState
FROM sys.database_permissions PER
INNER JOIN sys.database_principals PRC ON PER.grantee_principal_id = PRC.principal_id
INNER JOIN sys.database_principals GRT ON PER.grantor_principal_id = GRT.principal_id
LEFT JOIN sys.objects OBJ ON PER.major_id = OBJ.object_id
LEFT JOIN sys.schemas SCH ON OBJ.schema_id = SCH.schema_id
LEFT JOIN sys.columns COL ON PER.major_id = COL.object_id AND PER.minor_id = COL.column_id
WHERE PER.major_id >= 0
ORDER BY PermClass
,ObjectName
,PrincName
,PermType
,PermName;


djj
Go to Top of Page
   

- Advertisement -