About Me

Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group from January 2009 to present. 517 Lafarge Ave. Louisville, CO 80027 7667 Dyer Road Louisville, CO 80027

Monday, September 17, 2012

Grant Permissions for Viewing Object Definitions

Here's a way to grant permissions to view object definitions.  Very handy when you don't want someone to have full sysadmin permissions.

-- View Definition Permissions
-- Group in domain is called Admin_ViewDefinition
--          or, specify a user individually
Use Admin

Exec sp_addrole 'db_ViewDefinition'
Exec sp_addrolemember @rolename = 'db_ViewDefinition',@membername = 'MyDomainName\Admin_ViewDefinition'
GRANT VIEW Definition TO db_ViewDefinition