Sql-server – GRANT CONTROL SERVER when user may be an entity owner

permissionssql serversql-server-2008-r2

I have a login called user1. He only has the role of public. I want to grant CONTROL SERVER to him. So from sa I run this query

USE [master]

GRANT CONTROL SERVER TO [user1]

Which gives me this error

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

The only reason I can think of that this error is getting hit, is the entity owner condition. user1 may be an entity owner.

My question is, what can I do to give this CONTROL SERVER permission to user1?

Best Answer

If you suspect the user is an entity owner, you can confirm that by running this query, which shows all objects in the current database, along with their respective owner. By default, objects are owned by the schema owner, however objects can have their ownership changed to any database principal.

SELECT ObjectName = s.name + N'.' + o.name
    , SchemaOwnerName = dps.name
    , ObjectOwnerName = dpo.name
FROM sys.objects o
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
    LEFT JOIN sys.database_principals dpo ON o.principal_id = dpo.principal_id
    LEFT JOIN sys.database_principals dps ON s.principal_id = dps.principal_id
ORDER BY s.name, o.name;

To show how this works, I've setup a quick MCVE:

--create a test user
CREATE USER OwnerTest WITHOUT LOGIN;

--create a table, and change the ownership to OwnerTest
CREATE TABLE dbo.Test (id int NOT NULL);
ALTER AUTHORIZATION ON dbo.Test TO OwnerTest;

--check the list of owners
SELECT ObjectName = s.name + N'.' + o.name
    , SchemaOwnerName = dps.name
    , ObjectOwnerName = dpo.name
FROM sys.objects o
    INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
    LEFT JOIN sys.database_principals dpo ON o.principal_id = dpo.principal_id
    LEFT JOIN sys.database_principals dps ON s.principal_id = dps.principal_id
WHERE (dps.name <> s.name OR dpo.name IS NOT NULL)
ORDER BY s.name, o.name;

The results:

╔════════════╦═════════════════╦═════════════════╗
║ ObjectName ║ SchemaOwnerName ║ ObjectOwnerName ║
╠════════════╬═════════════════╬═════════════════╣
║ dbo.Test   ║ dbo             ║ OwnerTest       ║
╚════════════╩═════════════════╩═════════════════╝

Attempt to drop the user results in an error:

DROP USER OwnerTest;

Msg 15183, Level 16, State 1, Line 18
The database principal owns objects in the database and cannot be dropped.

If we drop the table first, then the user, the operation succeeds:

DROP TABLE dbo.Test;
DROP USER OwnerTest;

Commands completed successfully.

Related Question