How to grant permission to view database objects definition?

Scenario:
Customer asked DBA to grant permission to view recently created stored procedure script in SQL.
How will we do?

Solution:
For example the stored procedures sp1, sp2 and sp3 are created recently. User asking view access only for stored procedure objects .Not executes permission.

How will you do?

By using this query, we can do.

USE databasename
GO
GRANT VIEW DEFINITION ON sp1 TO sqlloginname;
GRANT VIEW DEFINITION ON sp1 TO sqlloginname;
GRANT VIEW DEFINITION ON sp3 TO sqlloginname;


View definition role allows user to view the corresponding objects

Any other methods?

Comments

Popular posts from this blog

SSIS2008R2 Error: 0xC0209303 at Package:

SQL2008R2 Error: The remote copy of database "XXXXX" has not been rolled forward to a point in time that is encompassed in the local copy of the database log. (Microsoft SQL Server, Error: 1412)