I have a user that have db_datareader role in SQL Server 2008 I need this user to be able to see Stored Procedures, functions, table scripts, etc.
What role is that? I don't want user to RUN DDL scripts (alter tables or procedures). I just want user to be able to read anything in database.
Thank you!
You're looking for VIEW DEFINITION.
You can grant a principal VIEW DEFINITION at various scopes (server, database, schema, specific object).
Example for a stored procedure:
Use MyDB GRANT VIEW DEFINITION ON p_MyProc TO SomeUser For all objects in a database:
Use MyDB GRANT VIEW DEFINITION TO SomeUser For all objects on the server:
USE master GRANT VIEW ANY DEFINITION TO SomeUSer Note: when granting permissions at the server level the principal must also have access at the database level in order to view definitions within the database.
