What should I grant a user / role to allow that user to view SP/table/views/etc source codes in SQL2008, besides granting DB_OWNER and / or DDL_ADMIN? Thanks
I understand the need to grant DB_OWNER or DDL_ADMIN to view definitions. The question is, can we grant anything less than fixed DB roles to users that will allow them to view?
Refer related thread:
Permissions to view Stored Procedure Property
To grant permissions on a stored procedure
- In Object Explorer, connect to an instance of SQL Server 2005 Database Engine and then expand that instance.
- Expand Databases, expand the database in which the stored procedure belongs, and then expand Programmability.
- Expand Stored Procedures, right-click the procedure to grant permissions on, and then click Properties.
- From Stored Procedure Properties, select the Permissions page.
- To grant permissions to a user, database role, or application role, click Add.
- In Select Users or Roles, click Object Types to add or clear the users and roles you want.
- In the Explicit Permissions grid, select the permissions to grant to the specified user or role. For a description of the permissions, see Permissions.
- Selecting Grant indicates the grantee will be given the specified permission. Selecting Grant With indicates that the grantee will also be able to grant the specified permission to other principals.
That's helpful!
I'm guessing this will apply to Functions / SPs / UDF types?
Tables and views are still not viewable, correct?
- Edited by Ami2013 9 hours 51 minutes ago
That's helpful!
I'm guessing this will apply to Functions / SPs / UDF types?
Tables and views are still not viewable, correct?
- Edited by Ami2013 Thursday, September 10, 2015 9:13 PM
Kalman is correct if we understand you properly. The VIEW DEFINITION permission gives a user the ability to see the definition of the object. You can grant that for a single object (GRANT VIEW DEFINITION ON OBJECT::xyz TO user;) or to a whole schema (tables, views, procs, etc) (GRANT VIEW DEFINITION ON SCHEMA::schema1 TO user;) or to the entire database (GRANT VIEW DEFINITION ON DATABASE::dbname TO user;)
VIEW DEFINITION does not grant the ability to EXEC, SELECT, INSERT, UPDATE, or DELETE.