Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips













































   Got a SQL tip?
            We want to know!

Granting View Definition Permission to a User or Role in SQL Server

MSSQLTips author Nitin Rana By:   |   Read Comments (4)   |   Related Tips: More > Views

Problem
In SQL Server 2005 by default users of a database that are only in the public role cannot see the definitions of an object while using sp_help, sp_helptext or the object_definition function.  Sometimes this is helpful to allow developers or other non-administrators to see the object definitions in a database, so they can create like objects in a test or development database.  Instead of granting higher level permissions, is there a way to allow users that only have public access the ability to see object definitions?

Solution
When issuing sp_help, sp_helptext or using the object_definition() function the following errors in SQL 2005 will occur if the user does not have permissions to the see the object metadata.   Here are a couple of examples of these errors.

EXEC sp_help Customer
Msg 15009, Level 16, State 1, Procedure sp_help, Line 66
The object 'Customer' does not exist in database 'MSSQLTIPS' or is invalid for this operation.

A select against the OBJECT_DEFINITION function will return a value of NULL if the user does not have permissions to see the meta data.

SELECT object_definition (OBJECT_ID(N'dbo.vCustomer'))
NULL

By default users were able to see object definitions in SQL Server 2000, but in SQL Server 2005 this functionality was removed to allow another layer of security.  By using a new feature called VIEW DEFINITION it is possible to allow users that only have public access the ability to see object definitions.

To turn on this feature across the board for all databases and all users you can issue the following statement:

USE master
GO
GRANT VIEW ANY DEFINITION TO PUBLIC

To turn on this feature across the board for all databases for user "User1" you can issue the following statement:

USE master
GO
GRANT VIEW ANY DEFINITION TO User1

To turn this feature on for a database and for all users that have public access you can issue the following:

USE AdventureWorks
GO
GRANT VIEW Definition TO PUBLIC

If you want to grant access to only user "User1" of the database you can do the following:

USE AdventureWorks
GO
GRANT VIEW Definition TO User1

To turn off this functionality you would issue the REVOKE command such as one of the following:

USE master 
GO 
REVOKE VIEW ANY DEFINITION TO User1 

-- or

USE AdventureWorks 
GO 
REVOKE VIEW Definition TO User1 

If you want to see which users have this access you can issue the following in the database.

USE AdventureWorks
GO
sp_helprotect

Here are two rows that show where the VIEW DEFINITION action has been granted.  The first on a particular object and the second for all objects in the database.

To take this a step further, if you do not want to grant this permission on all objects the following stored procedure can be used to grant this to all objects or particular objects in a database.  This is currently setup for all object types, but this can be changed by including less object types in the WHERE clause.

WHERE type IN ('P''V''FN''TR''IF''TF''U'

/*
Included Object Types are: 
P - Stored Procedure 
V - View 
FN - SQL scalar-function
TR - Trigger 
IF - SQL inlined table-valued function
TF - SQL table-valued function
U - Table (user-defined)
*/ 

To use this, you can create this stored procedure in your user databases and then grant the permissions to the appropriate user instead of making things wide open for a user or all users.  Just replace ChangeToYourDatabaseName for your database before creating.

USE ChangeToYourDatabaseName 
GO 
CREATE PROCEDURE usp_ExecGrantViewDefinition 
(@login VARCHAR(30)) 
AS 
/*
Included Object Types are: 
P - Stored Procedure 
V - View 
FN - SQL scalar-function
TR - Trigger 
IF - SQL inlined table-valued function
TF - SQL table-valued function
U - Table (user-defined)
*/ 
SET NOCOUNT ON 

CREATE TABLE 
#runSQL
(runSQL VARCHAR(2000) NOT NULL) 

--Declare @execSQL varchar(2000), @login varchar(30), @space char (1), @TO char (2) 
DECLARE @execSQL VARCHAR(2000), @space CHAR (1), @TO CHAR (2

SET @to 'TO'
SET @execSQL 'Grant View Definition ON ' 
SET @login REPLACE(REPLACE (@login'['''), ']''')
SET @login '[' @login ']'
SET @space ' '

INSERT INTO #runSQL 
SELECT @execSQL schema_name(schema_id) + '.' [name] @space @TO @space @login 
FROM sys.all_objects s 
WHERE type IN ('P''V''FN''TR''IF''TF''U'
AND 
is_ms_shipped 
ORDER BY s.types.name 

SET @execSQL '' 

Execute_SQL: 

SET ROWCOUNT 

SELECT @execSQL runSQL FROM #runSQL

PRINT @execSQL --Comment out if you don't want to see the output

EXEC (@execSQL)

DELETE FROM #runSQL WHERE runSQL @execSQL

IF EXISTS (SELECT FROM #runSQL
   
GOTO Execute_SQL 

SET ROWCOUNT 0

DROP TABLE #runSQL 

GO 

Once this procedure has been created you can grant the permissions as follows. This example grants view definition to a user "userXYZ" in "MSSQLTIPS" Database for all object types that were selected.

USE MSSQLTIPS
GO
EXEC usp_ExecGrantViewDefinition 'userXYZ'
GO 

Next Steps

For additional information on the topics discussed refer to these keywords in SQL Server 2005 Books Online:

  • sp_helptext
  • sp_help
  • object_definition
  • Object_Id
  • Create Procedure
  • Schema_Name
  • Schema_ID
  • Replace
  • GRANT
  • VIEW DEFINITION



Last Update: 9/29/2008


About the author
MSSQLTips author Nitin Rana
Nitin Rana is a Microsoft Certified Professional and currently has over 12 years of IT experience with the SQL server core components.

View all my tips


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Comments and Feedback:
Monday, September 27, 2010 - 5:34:56 PM - Gabriel Read The Tip
I tried:

USE master
GO
GRANT VIEW ANY DEFINITION TO User1


And it seems to have no effect. It works indivually in one database at a time 

use Database_1
GRANT VIEW  DEFINITION TO [MyDomain\MySQL_WindowsGroup]

(SQL 2005 sp3)

thoughts ?


Thursday, January 19, 2012 - 12:44:35 PM - Arvind Read The Tip

Hi Nitin,

How are you doing? Nitin I need some help in SQL 2000 for security, can you take a look and see if you can please help me....

Here's what iam looking for :

I want to create a stored procedure in SQL 2000 which will create Role in a database(as per what i pass the name), after creating the role assign permission to that Role, let's say for all tables in a database SELECT permission or Exec to all the proc.

After assigning permission to the Role, we need to attach that Role to a USER in the database.

If you remember HarperCollins structure, here what i'm looking for :

Let's say there is a Login with your name : ITServices\Nitin

Now i need to create a role(from the stored procedure), define Role some permission, let's say the Role name is READER, we need to give SELECT to that ROLE. After its done we need to assign that ROLE to an USER.

I don't know if below code will help you but it will give you and idea of what iam trying to do!!

Create  PROCEDURE usp_testing_roles @loginame varchar(50), @passwd varchar (20), @RoleName varchar(10)
                   
AS
BEGIN

exec  sp_addlogin @loginame ,@passwd

exec sp_grantdbaccess @loginame ,@name_in_db = @loginame

EXEC sp_addrole @RoleName

EXEC sp_addrolemember @RoleName , @loginame

--exec sp_addrolemember 'db_datawriter' ,@loginame


EXEC sp_grantdbaccess  @loginame

--EXEC sp_addrolemember @RoleName , @loginame


END


We need to run the above proc as : exec usp_testing_roles 'TestUser','asdfgt','Reader'


Now my issue iam facing is that iam not able to give permission to a ROLE. or should i said that i know how to do it but not able to understand how to do. I have some idea for that also i.e

select   'GRANT EXECUTE ON dbo.' + name + ' TO READER'
from     sysobjects
where  type  =  'U'

select   'GRANT EXECUTE ON dbo.' + name + ' TO READER'
from     sysobjects
where  type  =  'P'


Using the above code we can get all the tables list in the data(it will spit out all the code for grating permission), now if we can use that code in a cursor and grant permission to the role, i guess the work is completed.

Can you please take a look when you have sometime and help me out!

I tried running you query in SQL 2000 but it is throwing me error "'schema_name' is not a recognized function name."

Arvind.

 


Friday, June 22, 2012 - 5:48:53 AM - Dinesh Vishe Read The Tip

SET ARITHABORT OFF
SET ANSI_WARNINGS OFFcan we use in View ??


Tuesday, August 28, 2012 - 9:52:24 AM - boris tyukin Read The Tip

very nice post that helps to convince DBA team :) apparently many DBAs are not aware of this and think that you need to have exec permissions to view the source code of the objects.



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.