Sql-server – Is there DDL viewer role in SQL Server

sql serversql-server-2008-r2

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!

Best Answer

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.

Related Topic