Thursday, March 18, 2021

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

All I did was this:    GRANT SHOWPLAN TO [domain\userName];
And the server returned this message:

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

So what does that mean?  Per MSFT, this message occurs when you try to grant or revoke permissions for the following database principals:

    yourself, sa, dbo, entity owner, information_schema, sys

Weird. The [domain\userName] is not any of these users. Even more weird, the [domain\userName] was not even a user in the database yet.  So I dig a little further and found that this user was mapped to dbo within the database I was trying to GRANT SHOWPLAN to:


How to correct?

USE master;
ALTER AUTHORIZATION ON DATABASE::[databaseName] TO [sa]; -- or the desired owner
USE databaseName;
CREATE USER [domain\userName] FOR LOGIN [domain\userName] WITH DEFAULT_SCHEMA=[dbo];

Then my GRANT SHOWPLAN completes without error:

GRANT SHOWPLAN TO [domain\userName];

Commands completed successfully.
Completion time: 2021-03-18T14:48:18.5162709-05:00

ALTER AUTHORIZATION is the new version of sp_changedbowner, which has been deprecated.  More details here:

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-authorization-transact-sql?view=sql-server-ver15

Many people say that there are risks to granting SHOWPLAN to users, but I disagree.  For the authorized user, of course, I believe it is ideal for them to have this permission so that they can become aware of the resource overhead of their statements, and potentially help them to write better code. 😏😏

More details on SHOWPLAN:

https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-database-permissions-transact-sql?view=sql-server-ver15


Hope I've helped!