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

9
votes

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?

1
sql-serversql-server-2008permissions
What roles / permissions does userName currently have? – mr.Reband
What rule are you member of? – Amir Keshavarz
I was trying to grant userName exec permission on some stored procedures. To answer, Amirreza Keshavarz's question, I said I have sysadmin and serveradmin roles. – John

1 Answers

7
votes

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.