验证SQL Server登录账号是否具备创建数据库权限的SQL查询咨询

可以，但原查询存在一处语法逻辑错误需要修正，修正后就能准确判断登录账号是否具备CREATE DATABASE权限。

原查询代码 #

select the_login.name as the_login_name,
  the_login.principal_id as the_login_principal_id,
  case sysadmin_role_assoc.role_principal_id
    when null then 0 else 1 end as user_has_sysadmin_role,
  case serveradmin_role_assoc.role_principal_id
    when null then 0 else 1 end as user_has_serveradmin_role,
  case dbcreator_role_assoc.role_principal_id
    when null then 0 else 1 end as user_has_dbcreator_role,
  case the_diskadmin_role_assoc.role_principal_id
    when null then 0 else 1 end as user_has_diskadmin_role,
  case alter_any_database_perm.grantee_principal_id
    when null then 0 else 1 end as user_has_alter_any_database

from master.sys.server_principals the_login

  left outer join  sys.server_principals the_sysadmin_role
   on (the_sysadmin_role.name = 'sysadmin')

  left outer join  sys.server_role_members sysadmin_role_assoc
   on (the_login.principal_id = sysadmin_role_assoc.member_principal_id
   and the_sysadmin_role.principal_id = sysadmin_role_assoc.role_principal_id)

  left outer join  sys.server_principals the_serveradmin_role
   on (the_serveradmin_role.name = 'serveradmin')

  left outer join  sys.server_role_members serveradmin_role_assoc
   on (the_login.principal_id = serveradmin_role_assoc.member_principal_id
   and the_serveradmin_role.principal_id = serveradmin_role_assoc.role_principal_id)

  left outer join  sys.server_principals the_dbcreator_role
   on (the_dbcreator_role.name = 'dbcreator')

  left outer join  sys.server_role_members dbcreator_role_assoc
   on (the_login.principal_id = dbcreator_role_assoc.member_principal_id
   and the_dbcreator_role.principal_id = dbcreator_role_assoc.role_principal_id)

  left outer join  sys.server_principals the_diskadmin_role
   on (the_diskadmin_role.name = 'diskadmin')

  left outer join  sys.server_role_members the_diskadmin_role_assoc
   on (the_login.principal_id = the_diskadmin_role_assoc.member_principal_id
   and the_diskadmin_role.principal_id = the_diskadmin_role_assoc.role_principal_id)

  left outer join sys.server_permissions alter_any_database_perm
   on (the_login.principal_id=alter_any_database_perm.grantee_principal_id
   and alter_any_database_perm.permission_name = 'alter any database'
   and alter_any_database_perm.state_desc='grant')

  where the_login.name='sa'; 

关键修正点 #

原查询中所有CASE语句使用when null的写法错误，SQL里不能用=或when直接判断null值，必须改用is null。比如将：

case sysadmin_role_assoc.role_principal_id when null then 0 else 1 end

修改为：

case when sysadmin_role_assoc.role_principal_id is null then 0 else 1 end

所有5个CASE语句都需要做此修正，否则会导致判断逻辑失效，所有角色/权限状态都会被错误判定为1。

判断逻辑说明 #

修正后的查询通过以下维度验证权限：

• sysadmin角色：拥有服务器最高权限，完全具备创建数据库的能力
• serveradmin角色：负责服务器级配置，包含创建数据库的权限
• dbcreator角色：专门用于创建和管理数据库的内置角色
• diskadmin角色：负责磁盘资源管理，具备创建数据库的权限
• ALTER ANY DATABASE权限：直接授予创建、修改任意数据库的权限

只要查询结果中user_has_sysadmin_role、user_has_serveradmin_role、user_has_dbcreator_role、user_has_diskadmin_role、user_has_alter_any_database这五列任意一列值为1，就说明目标登录账号可以执行CREATE DATABASE命令。

适用场景 #

该查询适用于SQL Server数据库自动化创建场景，用来提前验证输入的登录账号是否合规；实际创建数据库时，还需要提供SQL Server主机名、登录名及密码。

内容的提问来源于stack exchange，提问作者Null Pointers etc.