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 procedures, 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:
Grant public access to all users across all databases:
USE master
GO
GRANT VIEW ANY DEFINITION TO PUBLIC
Grant access to a particular user across all databases
USE master
GO
GRANT VIEW ANY DEFINITION TO <User_name>
GO
GRANT VIEW ANY DEFINITION TO <User_name>
Grant public VIEW DEFINITION access to a database
USE Shop
GO
GRANT VIEW ANY DEFINITION TO PUBLIC
Grant VIEW DEFINITION access to a particular user to a
database
USE Shop
GO
GRANT VIEW ANY DEFINITION TO <User_name>
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:
Post a Comment