Check if role exists in DB? Check if role exists in DB? sql sql

Check if role exists in DB?



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

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 NULLBEGIN    -- Add user;ENDELSE 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