Sql-server – Identify why a user can drop a database

permissionssql-server-2008

I have a sql server user that has the ability to drop any database. I've been running the code below to check the rights that the user has in SQL Server but have not been able to identify how the user has the ability to drop databases. Is there a sql script that can help me identify how this user can drop dbs? Is there a command to deny them dropping any databases? (SSMS is not showing the user as part of the dbcreator role)

select USER_NAME(p.grantee_principal_id) AS principal_name,
    dp.type_desc AS principal_type_desc,
    p.class_desc,
    OBJECT_NAME(p.major_id) AS object_name,
    p.permission_name,
    p.state_desc AS permission_state_desc 
from    sys.database_permissions p
inner   JOIN sys.database_principals dp
on     p.grantee_principal_id = dp.principal_id
order by principal_name

The output of the query above provides the following three records for the user if it is helpful

class_desc object_name permission_name permission_state_desc
OBJECT_OR_COLUMN xp_cmdshell EXECUTE GRANT
DATABASE NULL CONNECT GRANT
DATABASE NULL CREATE DATABASE GRANT

Best Answer

The query you've got there will list only permissions for the database against which you run it. One way to get permission to drop a database is ALTER ANY DATABASE, which is a server-level permission. To check those, try this query:

SELECT 
  [srvprin].[name] [server_principal],
  [srvprin].[type_desc] [principal_type],
  [srvperm].[permission_name],
  [srvperm].[state_desc] 
FROM [sys].[server_permissions] srvperm
  INNER JOIN [sys].[server_principals] srvprin
    ON [srvperm].[grantee_principal_id] = [srvprin].[principal_id]
WHERE [srvprin].[type] IN ('S', 'U', 'G')
ORDER BY [server_principal], [permission_name];

In other words, the user might be getting the permission to drop databases at the server login level rather than the database user level.

Related Solutions

Sql-server – User can alter procedures without permission

Do you see anything that has been granted or role membership that shouldn't exist?

EXECUTE AS USER = N'your user';
GO
SELECT * FROM sys.fn_my_permissions(NULL, NULL);
GO
REVERT;
GO

SELECT * FROM sys.database_permissions
WHERE grantee_principal_id = USER_ID(N'your user');
GO

SELECT r.name
FROM sys.database_role_members AS m
INNER JOIN sys.database_principals AS p
ON m.member_principal_id = p.principal_id
INNER JOIN sys.database_principals AS r
ON m.role_principal_id = r.principal_id
WHERE p.name = N'your user';

SELECT r.name
FROM sys.server_role_members AS m
INNER JOIN sys.server_principals AS p
ON m.member_principal_id = p.principal_id
INNER JOIN sys.server_principals AS r
ON m.role_principal_id = r.principal_id
WHERE p.name = N'your login';

It is possible these developers are in the db_owner role without your knowledge or have been granted ALL on the dbo or another schema.

Make sure you execute these statements in the right database, and also validate that your users are logging in as who they say they are logging in as. Perhaps they have the sa password and you don't know it. Finally, you should make sure that the database principal maps to the correct server-level login.

SELECT dp.name, sp.name
FROM sys.database_principals AS dp
FULL OUTER JOIN sys.server_principals AS sp
ON dp.sid = sp.sid
WHERE 
(
  dp.type <> 'R' 
  OR sp.type NOT IN ('C', 'K')
)
ORDER BY 
  dp.name COLLATE DATABASE_DEFAULT + sp.name COLLATE DATABASE_DEFAULT DESC;

Sql-server – Hidden user rights

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;

Best Answer

Related Solutions

Sql-server – User can alter procedures without permission

Sql-server – Hidden user rights

Related Question