Check if user is a member of dbo role in SQL Server

Question

I need a T-SQL statement to check if a user is a member of a database role in SQL Server. In particular, I need to know if the user is a member of the dbo role, because then I do not need to provide additional privileges to this user.

If I try to add additional privileges when the user disconnects and my script does not work ...

Answer 1

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

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

$ (ContentAccount) is, of course, a parameterization that has a domain and username!

This solves my problem because when we deploy a new database we assign permissions manually. But in development environments where the user we are trying to add is already sysadmin, they fail. Therefore, if we check sysadmin membership, which is enough to cover the dev server script.

Then I do this to verify ownership:

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

Answer 2

IS_ROLEMEMBER ?

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

Or, if the request is for another user:

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

Answer 3

Here is an example from the MSDN page in IS_MEMBER :

 -- 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' 

Answer 4

IS_SRVROLEMEMBER is the function you want.

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

Link