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

permissionssql serversql-server-2008

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?

Best Answer

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.

Related Topic