Counter of Festivals

Ashok Blog for SQL Learners and Beginners and Experts

Thursday, 22 May 2014

How do you grant execute permission to a single SP ? & How to view permission exists in SP?

How do you grant execute permission for a single stored procedure? and How to view permission has given correctly or not in objects?


Grant Permission Syntax:

GRANT EXECUTE ON <schema>.<object> to <user>;

GRANT EXECUTE ON dbo.getuserdetails TO testuser

getuserdetails----------SP Name
testuser-------------------Username

View Permission in Objects:

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 ('getuserdetails ')-------------------------Objects name
order by OBJECT_NAME,PRINCIPAL_NAME