Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
Is this user dropping databases? –  Zane Dec 3 '12 at 18:16
    
I can drop databases as that user. Yes. If you are asking if anyone has used the account meliciously I'd rather not wait to see. –  Lumpy Dec 3 '12 at 18:20
    
What is the general level of permission this user has? Also is it only certain Databases or is this server wide? –  Zane Dec 3 '12 at 18:31
2  
Look at which permissions are required (msdn.microsoft.com/en-us/library/ms178613.aspx) and reverse engineer it. –  Thomas Stringer Dec 3 '12 at 18:37
    
+1 @ThomasStringer –  Zane Dec 3 '12 at 18:48

3 Answers 3

up vote 4 down vote accepted

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.

share|improve this answer
1  
It was the Alter any Database permission –  Lumpy Dec 3 '12 at 21:16
    
Yeah, definitely not a permission you want Joe in Accounting to have. –  KeithS Dec 4 '12 at 0:24

Is there a sql script that can help me identify how this user can drop dbs?

I have used this a few times with some good results, source of the code below can be found here:


SELECT SP1.[name] AS 'Login', 'Role: ' + SP2.[name] COLLATE DATABASE_DEFAULT AS 'ServerPermission'  
FROM sys.server_principals SP1 
  JOIN sys.server_role_members SRM 
    ON SP1.principal_id = SRM.member_principal_id 
  JOIN sys.server_principals SP2 
    ON SRM.role_principal_id = SP2.principal_id 
UNION ALL 
SELECT SP.[name] AS 'Login' , SPerm.state_desc + ' ' + SPerm.permission_name COLLATE DATABASE_DEFAULT AS 'ServerPermission'  FROM sys.server_principals SP  
  JOIN sys.server_permissions SPerm  
    ON SP.principal_id = SPerm.grantee_principal_id  
ORDER BY [Login], [ServerPermission];

Is there a command to deny them dropping any databases?

Going on documentation here, the security requirements for a user to drop a database are stated as:

Requires the CONTROL permission on the database, or ALTER ANY DATABASE permission, or membership in the db_owner fixed database role

You can explictily deny the permission to one mentioned above but understand that the level you deny it at may not take affect like you think. I recall reading a white paper that walked through how SQL Server goes about validating permissions of a user upon connection but cannot locate it right now. If I recall I may deny them connection to a database but the fact the user is part of the sysadmin role takes precedence.

I would look at auditing specifically for the DROP DATABASE command to be safe.

share|improve this answer
    
it was the alter any database permission. Thank you. –  Lumpy Dec 3 '12 at 20:08

I would suggest running this query on the master

SELECT  
    [UserName] = CASE princ.[type] 
                    WHEN 'S' THEN princ.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                 END,
    [UserType] = CASE princ.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                 END,  
    [DatabaseUserName] = princ.[name],       
    [Role] = null,      
    [PermissionType] = perm.[permission_name],       
    [PermissionState] = perm.[state_desc],       
    [ObjectType] = obj.type_desc,--perm.[class_desc],       
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM    
    --database user
    sys.database_principals princ  
LEFT JOIN
    --Login accounts
    sys.login_token ulogin on princ.[sid] = ulogin.[sid]
LEFT JOIN        
    --Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col ON col.[object_id] = perm.major_id 
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN
    sys.objects obj ON perm.[major_id] = obj.[object_id]
WHERE 
    princ.[type] in ('S','U')
UNION
--List all access provisioned to a sql user or windows user/group through a database or application role
SELECT  
    [UserName] = CASE memberprinc.[type] 
                    WHEN 'S' THEN memberprinc.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                 END,
    [UserType] = CASE memberprinc.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                 END, 
    [DatabaseUserName] = memberprinc.[name],   
    [Role] = roleprinc.[name],      
    [PermissionType] = perm.[permission_name],       
    [PermissionState] = perm.[state_desc],       
    [ObjectType] = obj.type_desc,--perm.[class_desc],   
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM    
    --Role/member associations
    sys.database_role_members members
JOIN
    --Roles
    sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
JOIN
    --Role members (database users)
    sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
LEFT JOIN
    --Login accounts
    sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]
LEFT JOIN        
    --Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col on col.[object_id] = perm.major_id 
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN
    sys.objects obj ON perm.[major_id] = obj.[object_id]
UNION
--List all access provisioned to the public role, which everyone gets by default
SELECT  
    [UserName] = '{All Users}',
    [UserType] = '{All Users}', 
    [DatabaseUserName] = '{All Users}',       
    [Role] = roleprinc.[name],      
    [PermissionType] = perm.[permission_name],       
    [PermissionState] = perm.[state_desc],       
    [ObjectType] = obj.type_desc,--perm.[class_desc],  
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM    
    --Roles
    sys.database_principals roleprinc
LEFT JOIN        
    --Role permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col on col.[object_id] = perm.major_id 
                    AND col.[column_id] = perm.[minor_id]                   
JOIN 
    --All objects   
    sys.objects obj ON obj.[object_id] = perm.[major_id]
WHERE
    --Only roles
    roleprinc.[type] = 'R' AND
    --Only public role
    roleprinc.[name] = 'public' AND
    --Only objects of ours, not the MS objects
    obj.is_ms_shipped = 0
ORDER BY
    princ.[Name],
    OBJECT_NAME(perm.major_id),
    col.[name],
    perm.[permission_name],
    perm.[state_desc],
    obj.type_desc--perm.[class_desc] 

This should give you a pretty good idea of the roles that have access to your master database and see weather or not the user has any of those roles. You can also run this against any of your other databases to check the user's permissions on a database by database level. This should be an important tool to help track this down.

share|improve this answer
    
Nice script.... –  Shawn Melton Dec 3 '12 at 18:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.