Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Friday, 9 May 2014

To Know SQL Server Permissions of Objects like SPs, Views etc.

To Know SQL Server Permissions of Objects like SPs, Views etc.

TSQL:

SELECT USER_NAME(P.GRANTEE_PRINCIPAL_ID) AS PRINCIPAL_NAME,

DP.PRINCIPAL_ID,

DP.TYPE_DESC AS PRINCIPAL_TYPE_DESC,

P.CLASS_DESC,

OBJECT_NAME(P.MAJOR_ID) AS OBJECT_NAME,

P.PERMISSION_NAME,

P.STATE_DESC AS PERMISSION_STATE_DESC

FROM SYS.DATABASE_PERMISSIONS P

INNER JOIN SYS.DATABASE_PRINCIPALS DP

ON P.GRANTEE_PRINCIPAL_ID = DP.PRINCIPAL_ID
where OBJECT_NAME(P.MAJOR_ID) in ('all_employeeRough')
order by OBJECT_NAME,PRINCIPAL_NAME

Output: