Sunday, 16 July 2017

Granting View Definition permission in SQL Server

In SQL Server 2008, a user given as pubic permission does have access to view the stored procs, function, triggers and indexes through either sp_helptext or sp_help. In this case, the SQL administrator gives the permission to the user or role to read the precise stored proc, functions or triggers, rather than giving superior permissions to the databases.
By executing the below statement by either developer or non-admins, the following errors are encountered:

USE Shop
Go
Exec sp_helptext <function_name>

ERROR:
The object 'Shop' does not exist in database 'SQLArena' or is invalid for this operation.


However, to overcome the above-encountered error, the user or role is given with the view definition permission. The View definition permission for a user is given below:

To Grant public access to all users across all databases:

USE master
GO
GRANT VIEW ANY DEFINITION TO PUBLIC

To Grant access to a particular user across all databases
USE master
GO
GRANT VIEW ANY DEFINITION TO <User_name>

To Grant public VIEW DEFINITION access to a database

USE Shop
GO
GRANT VIEW ANY DEFINITION TO PUBLIC

To Grant VIEW DEFINITION access to a particular user to a database

USE Shop
GO
GRANT VIEW ANY DEFINITION TO <User_name>

To Grant VIEW DEFINITION access to a particular database and particular stored proc, function, or trigger

USE Shop
GO
GRANT VIEW DEFINITION ON <SP_name, Function_name, Trigger_name> TO <User_name>

The above accesses can be removed using the “REVOKE” command as shown below:
USE Shop
GO

REVOKE VIEW DEFINITION TO <User_name>

0 comments: