sqlserver查询数据库权限的sql语句

在 SQL Server 中，查询数据库权限涉及到检索有关数据库对象、用户和角色权限的信息。SQL Server 提供了多种系统视图和函数来查询权限。以下是详细的 SQL 语句示例，用于检查和查询数据库权限：

1. 查询所有数据库用户及其角色

要查询当前数据库中的所有用户及其所属角色，可以使用以下 SQL 语句：

sql
复制代码
SELECT 
    dp.name AS DatabaseUserName,
    dp.type_desc AS UserType,
    rp.name AS RoleName
FROM 
    sys.database_principals dp
LEFT JOIN 
    sys.database_role_members drm ON dp.principal_id = drm.member_principal_id
LEFT JOIN 
    sys.database_principals rp ON drm.role_principal_id = rp.principal_id
WHERE 
    dp.type IN ('S', 'U', 'G', 'K') -- S: SQL user, U: Windows user, G: Windows group, K: Certificate
ORDER BY 
    dp.name;

2. 查询某个用户的所有权限

如果需要查询特定用户的权限，可以使用以下语句。替换 'username' 为具体的数据库用户名：

sql
复制代码
SELECT 
    dp.name AS UserName,
    o.name AS ObjectName,
    p.permission_name AS PermissionName,
    p.state_desc AS PermissionState
FROM 
    sys.database_permissions p
INNER JOIN 
    sys.objects o ON p.major_id = o.object_id
INNER JOIN 
    sys.database_principals dp ON p.grantee_principal_id = dp.principal_id
WHERE 
    dp.name = 'username'
ORDER BY 
    dp.name, o.name, p.permission_name;

3. 查询数据库角色及其权限

查询所有数据库角色及其权限信息可以使用以下 SQL 语句：

sql
复制代码
SELECT 
    r.name AS RoleName,
    p.permission_name AS PermissionName,
    p.state_desc AS PermissionState
FROM 
    sys.database_role_members rm
INNER JOIN 
    sys.database_principals r ON rm.role_principal_id = r.principal_id
INNER JOIN 
    sys.database_permissions p ON r.principal_id = p.grantee_principal_id
ORDER BY 
    r.name, p.permission_name;

4. 查询特定表的权限

如果需要查询某个特定表的权限，可以使用以下语句。替换 'table_name' 为具体的表名：

sql
复制代码
SELECT 
    dp.name AS UserName,
    o.name AS TableName,
    p.permission_name AS PermissionName,
    p.state_desc AS PermissionState
FROM 
    sys.database_permissions p
INNER JOIN 
    sys.objects o ON p.major_id = o.object_id
INNER JOIN 
    sys.database_principals dp ON p.grantee_principal_id = dp.principal_id
WHERE 
    o.name = 'table_name'
ORDER BY 
    dp.name, p.permission_name;

5. 查询数据库的用户和角色成员

可以使用以下语句查看数据库中所有用户和角色成员的信息：

sql
复制代码
SELECT 
    dp.name AS UserName,
    r.name AS RoleName
FROM 
    sys.database_role_members drm
INNER JOIN 
    sys.database_principals dp ON drm.member_principal_id = dp.principal_id
INNER JOIN 
    sys.database_principals r ON drm.role_principal_id = r.principal_id
ORDER BY 
    dp.name, r.name;

总结

1. 所有用户及角色：通过 sys.database_principals 和 sys.database_role_members 视图查询数据库用户及其角色。
2. 特定用户权限：使用 sys.database_permissions 视图和 sys.objects 视图查询用户的具体权限。
3. 数据库角色权限：查询所有角色及其权限，通过 sys.database_role_members 和 sys.database_permissions 视图实现。
4. 特定表权限：查询特定表的权限，通过 sys.database_permissions 视图和 sys.objects 视图实现。
5. 用户和角色成员：查看数据库中用户和角色的成员关系。

关键字

SQL Server, 数据库权限, sys.database_principals, sys.database_permissions, sys.database_role_members, 用户权限, 角色权限, 查询语句