HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

SQL Server 2012: Give rights to read ddl but not modify?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
2012readsqlbutgiveddlrightsservernotmodify

Problem

What rights would I need to be give a user for them to be able to view all DDL code (such as stored procedure code) but not be able to edit any of it?

The only possibility seems to be DDLadmin, but that would allow a user to edit as well. Is there some other options, or perhaps a way to grant and then revoke part of DDLAdmin?

Solution

You can give VIEW DEFINITION at the object, schema or database level.

GRANT VIEW DEFINITION ON dbo.objectname TO [user/role];

GRANT VIEW DEFINITION ON SCHEMA::schemaname TO [user/role];

GRANT VIEW DEFINITION ON DATABASE::databasename TO [user/role];


http://msdn.microsoft.com/en-us/library/ms173848.aspx

Code Snippets

GRANT VIEW DEFINITION ON dbo.objectname TO [user/role];

GRANT VIEW DEFINITION ON SCHEMA::schemaname TO [user/role];

GRANT VIEW DEFINITION ON DATABASE::databasename TO [user/role];

Context

StackExchange Database Administrators Q#88790, answer score: 9

Revisions (0)

No revisions yet.