September 15, 2011 at 2:48 pm
We have cases where column-level permissions have been granted to certain users in a database.
Would anyone happen to have a query to list these permissions and the columns on which they are granted?
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 15, 2011 at 4:33 pm
See if this helps.
http://jasonbrimhall.info/2010/03/19/security-audit/
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 15, 2011 at 4:48 pm
If not, then try this
SELECT dp.grantee_principal_id,p.name AS UName
,dp.permission_name,c.name
,OBJECT_NAME(o.object_id) AS TabName
FROM sys.database_permissions dp
INNER JOIN Sys.objects O
ON dp.major_id = o.object_id
INNER JOIN sys.columns C
ON c.object_id = O.object_id
AND c.column_id = dp.minor_id
INNER JOIN sys.database_principals P
ON p.principal_id = dp.grantee_principal_id
Nothing too fancy for now.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 16, 2011 at 9:13 am
SQLRNNR (9/15/2011)
If not, then try this
SELECT dp.grantee_principal_id,p.name AS UName
,dp.permission_name,c.name
,OBJECT_NAME(o.object_id) AS TabName
FROM sys.database_permissions dp
INNER JOIN Sys.objects O
ON dp.major_id = o.object_id
INNER JOIN sys.columns C
ON c.object_id = O.object_id
AND c.column_id = dp.minor_id
INNER JOIN sys.database_principals P
ON p.principal_id = dp.grantee_principal_id
Nothing too fancy for now.
Thank you, that works well for me.
I added one more column (dp.state_desc) but this worked great!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]
September 16, 2011 at 9:14 am
Glad it works.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply