Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server: Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself

I am configuring a new SQL Server 2008 R2 and am getting the following error when I was trying to grant exec permission on stored procedures to users using the statement like

grant exec on [schemaName].[StoredProcedureName] TO userName

The error I am getting:

Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

I am able to grant other permissions such as select permission to synonyms. I am also able to creating logins and creating users based on the logins and grant the users datawriter and datareader roles.

I have the public, serveradmin and sysadmin server roles. I have db_owner role in the database where I was trying to grant the permissions.

I have never had this issue before.

What am I missing?

like image 546
John Avatar asked Nov 22 '13 16:11

John


People also ask

How to Revoke permissions in SQL server?

The REVOKE statement can be used to remove granted permissions, and the DENY statement can be used to prevent a principal from gaining a specific permission through a GRANT. Granting a permission removes DENY or REVOKE of that permission on the specified securable.

What are DBO permissions?

A member of the dbmanager role that creates a database, becomes the owner of that database, which allows that user to connect to that database as the dbo user. The dbo user has all database permissions in the database.

How to give Grant permission in SQL?

You can use the SQL GRANT statement to grant SQL SELECT, UPDATE, INSERT, DELETE, and other privileges on tables or views. The WITH GRANT OPTION clause indicates that JONES can grant to other users any of the SQL privileges you granted for the ORDER_BACKLOG table.


1 Answers

I figured out why I was getting the message:

Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

It was because the user I was trying to grant permissions to owns the schema of those objects.

For example, one of the stored procedures is mySchema.usp_CreateUser() and the userName is "appUser". The "appUser" owns the schema "mySchema". Since the user owns the schema, the user can execute the procedure. There is no need to grant the user the exec permission.

like image 134
John Avatar answered Sep 30 '22 21:09

John