Monday, July 4, 2011

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?

No comments:

Post a Comment

MYSQL:::Seamless Data Archiving: Exporting, Importing, and Pruning MySQL Tables

  Seamless Data Archiving: Exporting, Importing, and Pruning MySQL Tables Introduction: In the dynamic landscape of database management...