Hidden user rights

Question

I'm running SQL Server 2008 R2 SP2. I have a SQL Server user, that the application uses to connect to my database. I've removed the user from all roles except public and the login from all database roles.

When I log into my SQL Server, I'm able to connect to the database and although I can't read from any tables, I can still create and drop tables.

In querying

sys.database_role_members
sys.database_principals
sys.server_role_members
sys.server_principals

... I'm not able to find any roles associated with the SQL user.

Is there someplace else I can look to find out how this user has permission to create and drop tables?

What right needs to be revoked to remove this users ability to create and drop data objects?

Answer 1

I bet CREATE TABLE is one of the things that shows up here (replace foo with the login name you're concerned about):

SELECT p.[permission_name], p.class_desc
FROM sys.server_principals AS sp
INNER JOIN sys.database_principals AS dp
ON sp.sid = dp.sid
INNER JOIN sys.database_permissions AS p
ON dp.principal_id = p.grantee_principal_id
WHERE sp.name = N'foo'
AND p.state_desc = N'GRANT'
AND p.class_desc IN (N'DATABASE', N'SCHEMA');

For each database permission found that you don't want them to have, run a revoke (for example, for CREATE TABLE):

REVOKE CREATE TABLE TO foo;

And for each schema where they have explicit privileges (like ALTER) - just replace schema with the actual schema:

REVOKE ALTER ON SCHEMA::schema TO foo;

They may also own a schema (not dbo, but maybe their default schema or another schema), so check that:

SELECT s.name
FROM sys.server_principals AS sp
INNER JOIN sys.database_principals AS dp
ON sp.sid = dp.sid
INNER JOIN sys.schemas AS s
ON dp.principal_id = s.principal_id
WHERE sp.name = N'foo';

If this comes up with any (again, replace schema with the actual schema):

ALTER AUTHORIZATION ON SCHEMA::schema TO dbo;

Answer 2

I would check the following:

1. Check assumptions

   USE [ApplicationDB];
   
   SELECT 
       @@SERVERNAME AS [ServerName],
       @@SERVICENAME AS [Sql Instance],
       SUSER_SNAME() AS [Login - Connection],
       SUSER_NAME() AS [Login - Running as],
       USER_NAME() AS [User - Running as];
   
   EXECUTE AS LOGIN = N'foo';
   SELECT 
       @@SERVERNAME AS [ServerName],
       @@SERVICENAME AS [Sql Instance],
       SUSER_SNAME() AS [Login - Connection],
       SUSER_NAME() AS [Login - Running as],
       USER_NAME() AS [User - Running as];
   REVERT;
   GO
   

2. Double check db owner

   SELECT
     name
    ,SUSER_SNAME(D.owner_sid)
   FROM sys.databases AS D
   WHERE
     D.database_id = DB_ID()
   

3. Check effective permissions

   EXECUTE AS LOGIN = N'foo';
   SELECT 
       N'Database' AS [Scope Class],
       DB_NAME() AS [Scope Name],
       permission_name AS [Effective Permission in Scope],
       fbp.type,
       fbp.covering_permission_name
   FROM fn_builtin_permissions(N'DATABASE') AS fbp
   WHERE
       HAS_PERMS_BY_NAME(N'[PermissionsTest]', N'DATABASE',fbp.permission_name)<>0
   UNION ALL
   SELECT
       N'SCHEMA',
       s.name,
       permission_name AS [Effective Permission in Scope],
       fbp.type,
       fbp.covering_permission_name
   FROM sys.fn_builtin_permissions(N'SCHEMA') AS fbp
   CROSS JOIN sys.schemas AS S
   WHERE
       HAS_PERMS_BY_NAME(s.name, N'SCHEMA', fbp.permission_name) <> 0
       -- remove system schemas
       AND s.name NOT IN ('db_owner','db_accessadmin','db_securityadmin',
                           'db_ddladmin','db_backupoperator','db_datareader',
                           'db_datawriter','db_denydatareader', 'db_denydatawriter',
                           'INFORMATION_SCHEMA','sys', 'guest' /*,'dbo' */ ) 
   REVERT;
   

4. list assigned permissions

   SELECT
       DP.class_desc AS [Scope Class],
       CASE 
           WHEN class = 0 THEN 
               DB_NAME()
           WHEN class = 3 THEN
               SCHEMA_NAME(DP.major_id)
           WHEN minor_id = 0 THEN 
               OBJECT_NAME(major_id,DB_ID())
           ELSE 
               '<ignored>'
       END  AS [Scope Name],
       USER_NAME(COALESCE(DRM.role_principal_id,DP.grantee_principal_id)) AS grantee ,
       DP.permission_name AS [permission affected],
       DP.state_desc
   FROM sys.database_permissions AS DP
   LEFT JOIN sys.database_role_members AS DRM ON DP.grantee_principal_id = DRM.role_principal_id
   WHERE
       DP.grantee_principal_id <> 0 AND
       (
           DP.grantee_principal_id = USER_ID('foo') OR
           DRM.member_principal_id = USER_ID('foo') 
       )
   

   where foo is the user of interest