Sql-server – How to allow users to add members to a specific database role

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.

DENY trumps GRANT, so yeah, you can’t let people view plans if you’ve explicitly denied them the ability to alter trace. But just because you haven’t denied a right doesn’t mean it has been granted.

This example shows that your database users can run queries and collect the execution plans within a database where they have been granted SHOWPLAN (and without being added to the db_owner role), as long as the server-level login has not been explicitly denied the ability to ALTER TRACE.

It also shows that, unless you explicitly grant any trace-related permissions, they won't be able to read trace data, or even be able to tell there are traces running at all, unless they have inherited those rights in some other way (Windows group, server role, etc). So you don't need to explicitly deny ALTER TRACE in order to protect trace data from these users - just be aware that it is possible for them to can inherit it without an explicit grant.

USE master;
GO
CREATE LOGIN blob WITH PASSWORD = 'x', CHECK_POLICY = OFF;
GO
CREATE DATABASE floob;
GO
USE floob;
GO
CREATE USER blob FROM LOGIN blob;
GO
GRANT SHOWPLAN TO blob;
GO
CREATE TABLE dbo.splunge(plonk INT);
GO
GRANT SELECT ON dbo.splunge TO blob;
GO
EXECUTE AS USER = 'blob';
GO
SET SHOWPLAN_XML ON;
GO
-- plan is shown:
SELECT plonk FROM dbo.splunge;
GO
SET SHOWPLAN_XML OFF;
GO
-- error:
EXEC sp_trace_getdata @traceid = 1;
GO
-- empty result set:
SELECT * FROM sys.traces;
GO
REVERT;
GO
USE master;
GO 
DROP DATABASE floob;
DROP LOGIN blob;