1

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!

katit
  • 213
  • 4
  • 9

1 Answers1

2

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.

squillman
  • 37,883
  • 12
  • 92
  • 146