Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Check if role exists in DB?

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

like image 353
sanjeev40084 Avatar asked Feb 02 '10 14:02

sanjeev40084


People also ask

How do you check what roles are assigned to a user in SQL?

To find all the role assignments to users in SQL Server database, you can use the following query. SELECT r.name role_principal_name, m.name AS member_principal_name FROM sys. database_role_members rm JOIN sys. database_principals r ON rm.

What is exists command in SQL?

The EXISTS operator is used to test for the existence of any record in a subquery. The EXISTS operator returns TRUE if the subquery returns one or more records.


2 Answers

try:

IF DATABASE_PRINCIPAL_ID('role') IS NULL
BEGIN
  -- add user here
  CREATE ROLE role AUTHORIZATION MyUser;
END
like image 185
Yada Avatar answered Oct 06 '22 00:10

Yada


IF EXISTS 
(
  SELECT 1
    FROM sys.database_principals
    WHERE type_desc = 'DATABASE_ROLE'
    AND name = 'name'
)
BEGIN
  -- add user;
END
like image 32
Aaron Bertrand Avatar answered Oct 05 '22 22:10

Aaron Bertrand