Check if role exists in DB?

Question

I want to add users to the same role in more than one database. However the role may or may not be present in each database. How can I check if the role exists in each database and if it does, add users to that role?

e.g. IF role exists BEGIN Add user in role END

Please refer to this link. Probably the same thing you're looking for http://stackoverflow.com/questions/1201160/how-do-i-determine-if-a-database-role-exists-in-sql-server — madatanic, Feb 02 '10 at 17:08
Possible duplicate of [How do I determine if a database role exists in SQL Server?](https://stackoverflow.com/questions/1201160/how-do-i-determine-if-a-database-role-exists-in-sql-server) — Jeff Puckett, Sep 29 '17 at 20:00

Yada · Accepted Answer · 2010-02-02T15:01:00.407

18

try:

IF DATABASE_PRINCIPAL_ID('role') IS NULL
BEGIN
  -- add user here
  CREATE ROLE role AUTHORIZATION MyUser;
END

edited Feb 02 '10 at 15:01

answered Feb 02 '10 at 14:55

Yada

30,349
24
103
144

This fails if you have a user who happens to have the same "role" name – Alex Klaus Mar 23 '17 at 04:00
Note too that meta-data functions (like DATABASE_PRINCIPAL_ID()) will frequently, by design, use low-level blocking.... – Michael K. Campbell Feb 29 '20 at 04:28

score 8 · Answer 2 · answered Feb 02 '10 at 14:56

8

IF EXISTS 
(
  SELECT 1
    FROM sys.database_principals
    WHERE type_desc = 'DATABASE_ROLE'
    AND name = 'name'
)
BEGIN
  -- add user;
END

answered Feb 02 '10 at 14:56

Aaron Bertrand

272,866
37
466
490

score 5 · Answer 3 · answered Jan 28 '13 at 20:52

I can't comment yet, so I have to add this as an answer.

I like to be as specific as possible, which is why I prefer including the principal type. This is why I up voted Aaron's answer.

Using DATABASE_PRINCIPAL_ID could give unexpected results if there is another principal type with the same name as the role that you want to add. This is because DATABASE_PRINCIPAL_ID returns the ID number of a principal in the current database, not specifically for a principal with a type of database role.

Let's say that you have a user with the same name as the database role. The status of Yada's script would indicate success, but it would not add the role because there is already a principal with that name. However, Aaron's script would return the following error:

User, group, or role 'name' already exists in the current database.

I'd rather catch this issue early on (e.g. when the script is run) than later on (e.g. when my app is being used).

Here's what I'd normally use:

IF NOT EXISTS(SELECT NULL FROM sys.database_principals WHERE [name] = 'role_name' AND [type]='R')
BEGIN
    -- add user;
END

If I really want to handle this scenario and not show an error, I could use something like this:

DECLARE @RoleName sysname,
        @PrincipalType NVARCHAR(60);

SET @RoleName = 'role_name';
SET @PrincipalType = (SELECT type_desc FROM sys.database_principals WHERE [name] = @RoleName);

IF @PrincipalType IS NULL
BEGIN
    -- Add user;
END
ELSE IF @PrincipalType <> 'DATABASE_ROLE'
BEGIN
    --Deal with the issue as desired. Here we're printing out a warning. Important: The status will still indicate that the Query executed successfully when using PRINT to show warnings.
    PRINT 'WARNING: The ' + @RoleName + ' database role was not created. A principal already exists in the database with a type of ' + @PrincipalType + '.';
END

Check if role exists in DB?

3 Answers3

Linked