检查用户是否是SQL Server中dbo角色的成员

I need a T-SQL statement to check if a user is member of a database role in SQL Server. Specifically I need to know if the user is member of the dbo role, because then I don't have to grant additional authority to that user.

我需要一个T-SQL语句来检查用户是否是SQL Server中数据库角色的成员。具体来说,我需要知道用户是否是dbo角色的成员,因为那样我就不必向该用户授予额外的权限。

If I try to add additional authority when the user is dbo it fails, and my script fails...

如果我尝试在用户使用dbo时添加其他权限,则会失败,并且我的脚本会失败...

4 个解决方案

#1

IS_ROLEMEMBER?

IF IS_ROLEMEMBER ('db_owner') = 1
BEGIN
    PRINT 'Is owner'
END

Or, if querying for a different user:

或者,如果查询其他用户:

IF IS_ROLEMEMBER ('db_owner','other user') = 1
BEGIN
    PRINT 'Is owner'
END

#2

Here is an example from the MSDN page on the IS_MEMBER function:

以下是IS_MEMBER函数上MSDN页面的示例:

-- Test membership in db_owner and print appropriate message.
IF IS_MEMBER ('db_owner') = 1
   PRINT 'Current user is a member of the db_owner role'
ELSE IF IS_MEMBER ('db_owner') = 0
   PRINT 'Current user is NOT a member of the db_owner role'
ELSE IF IS_MEMBER ('db_owner') IS NULL
   PRINT 'ERROR: Invalid group / role specified'

#3

This is what I ended up doing: (edit based on comment)

这就是我最终做的事情:(根据评论进行编辑)

DECLARE @ISSYSADMIN INT
SET @ISSYSADMIN = (SELECT COUNT(1) 
                   FROM sys.syslogins 
                   WHERE sysadmin = 1 AND loginname = '$(ContentAccount)')

The $(ContentAccount) is of course a parametrization which has the user domain and name!

$(ContentAccount)当然是一个具有用户域名和名称的参数化!

This solves my problem because when we deploy a new database we are assigning permissions manually. But in development environments where the user we try to add already is sysadmin they fail. So if we check for sysadmin membership that is enough to cover the dev server scenario.

这解决了我的问题,因为当我们部署新数据库时,我们手动分配权限。但是在我们尝试添加的用户已经是sysadmin的开发环境中,它们会失败。因此,如果我们检查足以覆盖开发服务器方案的sysadmin成员资格。

Then I do this to check membership:

然后我这样做来检查会员资格:

IF (@ISSYSADMIN = 0)
BEGIN
  -- Add authority
END

#4

IS_SRVROLEMEMBER is the function you want.

IS_SRVROLEMEMBER是您想要的功能。

IF IS_SRVROLEMEMBER ('sysadmin','myuser') = 1 PRINT 'Is SysAdmin'

IF IS_SRVROLEMEMBER ('serveradmin','myuser') = 1 PRINT 'Is ServerAdmin'

Reference

#1