SQL Server – Msg 229, level 14, State 5, Line 1 The SELECT permission was denied on database object

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.

Leave a Reply

  

  

  


2 − 1 =