How do I determine if a database role exists in SQL Server?
I'm trying to figure out how I can check if a database role exists in SQL Server. I want to do something like this:
if not exists (select 1 from sometable where rolename='role')
begin
CREATE ROLE role
AUTHORIZATION MyUser;
end
What table/proc should I use here?
Solution 1:
SELECT DATABASE_PRINCIPAL_ID('role')
--or
IF DATABASE_PRINCIPAL_ID('role') IS NULL
USER_ID is deprecated and could break. CREATE ROLE indicates SQL 2005+ so it's OK
Solution 2:
if not exists (select 1 from sys.database_principals where name='role' and Type = 'R')
begin
CREATE ROLE role
AUTHORIZATION MyUser;
end