主页 / dba / 问题 / 29723
Accepted
Lumpy
Asked: 2012-12-04 10:07:38 +0800 CST

确定用户可以删除数据库的原因

  • 772

我有一个能够删除任何数据库的 sql server 用户。我一直在运行下面的代码来检查用户在 SQL Server 中拥有的权限，但无法确定用户如何能够删除数据库。是否有一个 sql 脚本可以帮助我确定该用户如何删除数据库？是否有命令拒绝他们删除任何数据库？（SSMS 未将用户显示为 dbcreator 角色的一部分）

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

如果有帮助，上面查询的输出为用户提供了以下三个记录

class_desc object_name permission_name permission_state_desc OBJECT_OR_COLUMN xp_cmdshell 执行 GRANT DATABASE NULL CONNECT GRANT
DATABASE NULL CREATE DATABASE GRANT

sql-server-2008 permissions

3 个回答

  1. 我建议在 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]

    这应该让您很好地了解有权访问您的主数据库的角色，并查看用户是否具有这些角色中的任何一个。您还可以针对任何其他数据库运行此程序，以按数据库级别检查用户对数据库的权限。这应该是帮助追踪的重要工具。

    • 6

  2. 是否有一个 sql 脚本可以帮助我确定该用户如何删除数据库？

    我已经使用了几次并取得了一些不错的效果，下面的代码源可以在这里找到：

    
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];

    是否有命令拒绝他们删除任何数据库？

    继续此处的文档，用户删除数据库的安全要求如下：

    需要对数据库的 CONTROL 权限，或 ALTER ANY DATABASE 权限，或 db_owner 固定数据库角色的成员身份

    您可以明确拒绝对上述权限的许可，但要了解您拒绝的级别可能不会像您想象的那样生效。我记得读过一篇白皮书，其中介绍了 SQL Server 如何在连接时验证用户的权限，但现在无法找到它。如果我记得我可能会拒绝他们连接到数据库，但用户是sysadmin角色的一部分这一事实优先。

    DROP DATABASE为了确保命令安全，我会专门查看审计。

    • 4
  3. Best Answer

    您在那里获得的查询将仅列出您运行它的数据库的权限。获得删除数据库权限的一种方法是更改​​任何数据库，这是服务器级别的权限。要检查这些，请尝试以下查询：

    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];

    换句话说，用户可能会在服务器登录级别而不是数据库用户级别获得删除数据库的权限。

    • 4

相关问题