0

I am not to good at writing SQL so please help with below as i am doing something wrong here

I need to alter a role say role_a to add member Domain/SQLAgent

The problem is how can i check or write a query to do this on SQL servers where there is one more role role_b only existing if role a is not there

Basically, how can i write a script to check on server if role a exist alter that role to add member and if role b then do same.

modifying as Q is not duplicate

I want to check if role_a exist then

ALTER ROLE [role_a] ADD MEMBER [Domain/SqlAgent]

else

ALTER ROLE [role_b] ADD MEMBER [Domain/SqlAgent]

Note@ Both the roles do no exist together

0
4

Your basic question boils down to "How can I tell if a given role exists in a database." All of the other code you already know how to do.

The easiest way is to query the sys.database_principals table to see if the role is there. Based on your sample question, here is a good check and update into the different roles:

IF EXISTS (
    SELECT * 
    FROM sys.database_principals 
    WHERE name = 'role_a' 
        and type = 'R'
        )
BEGIN   -- Role_A exists, add to role
    ALTER ROLE [role_a] ADD MEMBER [Domain/SqlAgent]
END
ELSE IF EXISTS (
    SELECT * 
    FROM sys.database_principals 
    WHERE name = 'role_b' 
        and type = 'R'
        )
BEGIN   -- Role_B exists, add to role
    ALTER ROLE [role_b] ADD MEMBER [Domain/SqlAgent]
END
3
  • Thanks . This is where the problem is . I ran and it failed with below Cannot add the principal 'Domain/SqlAgent', because it does not exist or you do not have permission. Please note either role_a or role_b exist, they are not there together Jan 26 '19 at 0:17
  • Is the login already there, or do you need to create the login as well? Jan 26 '19 at 0:20
  • Yes the login Domain/SqlAgent exist Jan 26 '19 at 0:21
3

If you want to check existing roles look at this table:

SELECT * FROM sys.database_principals
WHERE Type = 'R'

If you want to check existing role members try this query:

SELECT Role = r.name, Member = m.name 
FROM sys.database_role_members as rm
INNER JOIN sys.database_principals as r
    ON r.principal_id = rm.role_principal_id
INNER JOIN sys.database_principals as m
    ON m.principal_id = rm.member_principal_id

Not the answer you're looking for? Browse other questions tagged or ask your own question.