THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

Creating a Database Role to Allow Users to Script Object Definitions

In the past month I’ve answered a couple of questions similar to:

Is there a database role I can assign (other than dbo) that will allow a user to "see" stored procedures.  As it is, I have granted the user db_datareader so, from SSMS, they can see tables but no stored procedures.  Is there a way to "give" them readonly access to stored procedures?

and

What is the role that I need in SQL 2008 to be able to read the entire schema out of a database but not make changes to it?

The answer to both of these questions is that there is no built in database role that allows this kind of access.  The db_datareader role would provide access to see tables and views, but not to see the DDL code behind those objects, and it wouldn’t allow a user to see stored procedures or functions at all.  The ddladmin database role would provide access to see the objects, but also to change them as well, which is not the intent here.  To accomplish the desired result of this type of request requires that a new database role be created. 

To demonstrate this, lets create a test database with a couple of objects in it:

create database test
go
use test
go
create table test (rowid int identity)
go
create view testview
as
select rowid
from test
go
create procedure testproc
as
begin
select * from test
end
go
create function testfunction(@rowid int)
returns int
as
begin
declare @retval int
select @retval = rowid
from test
where rowid = @rowid
return(@retval)
end
go

Then we’ll create a test database user and assign that user to the db_datareader role in the test database.

USE [master]
GO
CREATE LOGIN [Test] WITH PASSWORD=N'test'
GO
USE [test]
GO
CREATE USER [Test] FOR LOGIN [Test]
GO
USE [test]
GO
EXEC sp_addrolemember N'db_datareader', N'Test'
GO

Then we’ll login to Object Explorer with the Test account and the only objects that can be seen are the tables and the view:

image

If you try to view the definition of the table it will succeed, however, if you try to script the definition of the view, an error will result:

image

To provide access to the Test login to view the Stored Procedure and Function, as well as to script the objects along with the view, we’ll need to create a user defined database role and GRANT that role the VIEW DEFINITION permission on the dbo Schema in the Test Database.

use test
go
CREATE ROLE [ddl_viewer]
GO
GRANT VIEW DEFINITION ON SCHEMA ::dbo TO ddl_viewer
GO

Then we’ll add the Test user in the database to the ddl_viewer role in the database:

USE [Test]
GO
EXEC sp_addrolemember N'ddl_viewer', N'Test'
GO

With this in place if we refresh the Object Explorer view for the database we’ll see that the Test user can now see all of the objects in the database:

image

In addition to this, the user can script out any object to see the definition of the object, but they can not execute any of the stored procedures or call any of the functions in the database directly.

image

Having a database role that allows users to view objects and DDL definitions in a database can be a valuable addition in SQL Server for a number of reasons.  For development based environments it allows support developers to ensure that the appropriate code has been released into production environments to support post release implementations.  For report writers, it allows them to validate that the version of a view that exists in Development matches that of production, and when using tools such as Data Dude or SQL Compare, it allows non-DBA’s to compare schema’s between production and test/development databases.

Published Wednesday, August 04, 2010 11:47 PM by Jonathan Kehayias

Comments

 

Ben Thul said:

I think that you can leave out the "on schema::dbo" in the grant statement and get everything in the database.

August 5, 2010 7:35 AM
Anonymous comments are disabled

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement