How do I determine if a database role exists in SQL Server?

Question

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?

gbn · Accepted Answer · 2012-02-01T07:55:27.887

99

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

edited Feb 01 '12 at 07:55

answered Jul 29 '09 at 15:37

gbn

2

@Klaus: try and create a role + user with the same name. It will fail. https://msdn.microsoft.com/en-us/library/ms187328.aspx "name" colun is unique – gbn Mar 23 '17 at 13:26

score 31 · Answer 2 · answered Jul 29 '09 at 15:42

31

if not exists (select 1 from sys.database_principals where name='role' and Type = 'R')
begin
CREATE ROLE role
    AUTHORIZATION MyUser;
end

answered Jul 29 '09 at 15:42

George Mastros

3

This fails if you have a user who happens to have the same "role" name. Odd, but you'd have to remove the "= 'R'" to work 100% – gbn Jul 29 '09 at 15:56
@Klaus what have I got to do with this? – gbn Mar 23 '17 at 13:26
Shouldn't there be `(select top 1 from...` at the beginning of condition? – Gucu112 Aug 27 '18 at 21:29
2

@Gucu112, top 1 is not necessary, and in fact would likely slow down the query (albeit very little). – George Mastros Aug 28 '18 at 12:28
Sorry, my bad. In fact I have double-checked and it depends of what you would like to achieve. In case mentioned by the author selecting just 1 as value is sufficient. – Gucu112 Aug 29 '18 at 02:17

2 Answers2