Permissions granted to users to view object definitions in SQL Server

In SQL Server, there are times when you need to give some logins (users) permission to view all or part of the objects (stored procedures, functions, views, tables) that define permissions to save. If a partial stored procedure, function, view is granted permission to view the definition, it is cumbersome as shown in the following script:

GRANT VIEW DEFINITION on your_procedure to USERNAME;

GRANT VIEW DEFINITION on your_function to USERNAME;

GRANT VIEW DEFINITION on Your_view to Useranem;

.....................................................

In the case of volume licensing, you can use the following script to generate an authorization script. Then execute the generated script:

Use DatabaseName;

GO

---grant a user permission to view a stored procedure definition

DECLARE @loginname VARCHAR (32);

SET @loginname = ' [Eopms_reader] '

SELECT ' GRANT VIEW DEFINITION on ' + schema_name (schema_id) + '. '

+ QUOTENAME (name) + ' to ' + @loginname + '; '

--Grant users permission to view custom function definitions

SELECT ' GRANT VIEW DEFINITION on ' + schema_name (schema_id) + '. '

+ QUOTENAME (name) + ' to ' + @loginname + '; '

From sys.objects

WHERE Type_desc in (' Sql_scalar_function ', ' sql_table_valued_function ',

' Aggregate_function ');

--Grant the user permission to view the view definition

SELECT ' GRANT VIEW DEFINITION on ' + schema_name (schema_id) + '. '

+ QUOTENAME (name) + ' to ' + @loginname + '; '

From Sys.views;

--Grant the user permission to view the table definition

+ QUOTENAME (name) + ' to ' + @loginname + '; '

from Sys.tables;

If you want to execute scripts directly and do not want to generate authorization scripts, you can use the following script to implement authorization. Of course, if you choose the database you want to authorize (use DatabaseName)

DECLARE @loginname VARCHAR (32);

DECLARE @sqlcmd NVARCHAR (MAX);

DECLARE @name sysname;

DECLARE @schema_id INT;

SET @loginname = ' [Kerry] '

DECLARE procedure_cursor Cursor Forward_only

For

SELECT schema_id, Name

From Sys.procedures;

OPEN Procedure_cursor;

FETCH NEXT from Procedure_cursor to @schema_id, @name;

---grant a user permission to view a stored procedure definition

While @ @FETCH_STATUS = 0

BEGIN

SET @sqlcmd = ' GRANT VIEW DEFINITION on ' + schema_name (@schema_id) + '. '

+ QUOTENAME (@name) + ' to ' + @loginname + '; '

--print @sqlcmd;

EXEC sp_executesql @sqlcmd;

FETCH NEXT from Procedure_cursor to @schema_id, @name;

END

CLOSE Procedure_cursor;

Deallocate procedure_cursor;

For

SELECT schema_id, Name

From sys.objects

WHERE Type_desc in (' Sql_scalar_function ', ' sql_table_valued_function ',

' Aggregate_function ');

--Grant users permission to view custom function definitions

OPEN Function_cursor;

FETCH NEXT from Function_cursor to @schema_id, @name;

BEGIN

SET @sqlcmd = ' GRANT VIEW DEFINITION on ' + schema_name (@schema_id) + '. '

+ QUOTENAME (@name) + ' to ' + @loginname + '; '

--print @sqlcmd;

EXEC sp_executesql @sqlcmd;

FETCH NEXT from Function_cursor to @schema_id, @name;

END

CLOSE Function_cursor;

Deallocate function_cursor;

DECLARE view_cursor Cursor Fast_forward

For

SELECT schema_id, name from Sys.views;

OPEN View_cursor;

FETCH NEXT from View_cursor to @schema_id, @name;

BEGIN

--Grant the user permission to view the view definition

SET @sqlcmd = ' GRANT VIEW DEFINITION on ' + schema_name (@schema_id) + '. '

+ QUOTENAME (@name) + ' to ' + @loginname + '; '

--print @sqlcmd;

EXEC Sys.sp_executesql @sqlcmd;

FETCH NEXT from View_cursor to @schema_id, @name;

END

CLOSE View_cursor;

Deallocate view_cursor;

DECLARE table_cursor Cursor Fast_forward

SELECT schema_id,name from Sys.tables;

OPEN Table_cursor;

FETCH NEXT from Table_cursor to @schema_id, @name;

While @ @FETCH_STATUS = 0

BEGIN

SET @sqlcmd = ' GRANT VIEW DEFINITION on ' + schema_name (@schema_id) + '. '

+ QUOTENAME (@name) + ' to ' + @loginname + '; '

--print @sqlcmd;

EXEC Sys.sp_executesql @sqlcmd;

FETCH NEXT from Table_cursor to @schema_id, @name;

END

CLOSE Table_cursor;

Deallocate table_cursor;

If a single user is authorized, then using the above method is OK, but a system, often encountered this authorization situation, then the use of the above method will become very cumbersome, in addition, when new tables, views, stored procedures, you need to modify the relevant authorization script, it becomes very cumbersome, you can create a database role , grant the permission to view the database object definition, and then create a new login to grant the new login name to the database role. This way, even if you have permission to change, you only need to adjust the permissions of the database role, you do not need to modify the login name. It is easy and easy to work with repetitive authorizations. For example, to grant the newly created login Kerry permission to view the database object definition, first create the database role View_definiton, then grant the database role View_definiton to view the defined permissions, and finally, after you create the login, Kerry, Simply grant the role View_definiton to Kerry.

Use AdventureWorks2014;

GO

CREATE ROLE [View_definiton] AUTHORIZATION [dbo]

GO

DECLARE @loginname VARCHAR (32);

SET @loginname = ' View_definiton '

SELECT ' GRANT VIEW DEFINITION on ' + schema_name (schema_id) + '. '

+ QUOTENAME (name) + ' to ' + @loginname + '; '

--Grant users permission to view custom function definitions

SELECT ' GRANT VIEW DEFINITION on ' + schema_name (schema_id) + '. '

+ QUOTENAME (name) + ' to ' + @loginname + '; '

From sys.objects

WHERE Type_desc in (' Sql_scalar_function ', ' sql_table_valued_function ',

' Aggregate_function ');

--Grant the user permission to view the view definition

SELECT ' GRANT VIEW DEFINITION on ' + schema_name (schema_id) + '. '

+ QUOTENAME (name) + ' to ' + @loginname + '; '

From Sys.views;

--Grant the user permission to view the table definition

SELECT ' GRANT VIEW DEFINITION on ' + schema_name (schema_id) + '. '

+ QUOTENAME (name) + ' to ' + @loginname + '; '

from Sys.tables;

ALTER ROLE [View_definiton] ADD MEMBER [Kerry]

GO

Permissions granted to users to view object definitions in SQL Server