To maintain data integrity of the database, DBA has to restrict unauthorized access to the table or database objects. Company has their own security policies to protect customer data that sometime make hard for end user to access any information.
Application developers use to write procedure to generate business report for client. While executing procedure, they got error saying “Select permission was denied on object “
Look at the exception below:
“Msg 229, level 14, State 5, Line 1 The SELECT permission was denied on the object ‘PORaisetbl’, database ‘Salesdb’, schema ‘dbo’.”
Grant select permission to user
Now we have to grant select permission without altering security policies of the client.
This can be done in two ways one is grant select permission to that user.
Sql server gives you some default database role like db_datareader which will gives user the select permission on database along this some more permission get assigns. To avoid such things, sql server offers commands to give and remove access from database.
You can make use of below commands for assigning select permission to specific user.
Grant, Deny and Revoke are simple commands to control the user access on database.
Grant -> It assign permission to user
Deny -> Used to restrict user access on database objects
Revoke -> Reassigning the permission to user for certain activity on database objects
Examples
Let’s go through with some examples of commands
I want to allow user to select and insert and update data in table Orders
GRANT Insert, update, select on Orders to username
Now I want to remove certain permissions of user, let say update. I will use Revoke command to remove update permission for user.
REVOKE UPDATE ON orders to username
If you want to restrict user to delete any data in table, you can make use to Deny command to perform this action.
DENY DELETE ON orders to username
Better to use grant, revoke or deny commands to assign any permission for database user as good practice.
Execute as owner
Here user is facing problem after executing stored procedure, you can use second method that is you can execute procedure either using Execute as owner specifies the statements inside the stored procedure that will use current owner credential to execute.
If the procedure does not have a specified owner, the owner of the schema of the stored procedure is used.
It uses execute as owner in the following scenario:
1. You want to change owner of the module without having to modify the module itself. That is, OWNER automatically maps to the current owner of the module at run time.
2. Owner is the explicit owner of the module or, if there is not an explicit owner, the owner of the schema of the module at the time.
3. Owner must be a singleton account and not a group or role, the ownership of the module cannot be changed to a group or role when the module specifies execute as owner and has an explicit owner.
4. The ownership of a schema cannot be changed to a role or group when it contains a module that specifies execute as owner and the modules does not have an explicit owner.
Can be used for function (except inline table-valued functions), stored procedure and DML triggers
Syntax:
exec|execute as caller|SELEF|OWNER|username
DDL triggers with Database Scope
EXEC|EXECUTE as CALLER|SELF|username
DDL Triggers with Server Scope and logon triggers
EXEC|EXECUTE as CALLER|SELF|loginname
Queues
EXEC|EXECUTE as SELF|OWNER|username
Make a practice to assign minimal permission to users.