CODEWITHSUNDEEP
sqlsql-serverdatabaseroles
sanjeev40084
sanjeev40084

Reputation: 9617

Check if role consists of particular user in DB?

I am trying to drop user from role in several databases. It is not sure that user can be in same role in all the databases. How can i check if the user is in the role and if it is drop the user from the role.

e.g. IF user exists in role BEGIN drop user from role END

Upvotes: 1

Views: 3282

Answers (2)

sanjeev40084
sanjeev40084

Reputation: 9617

This is what i did:

IF EXISTS(SELECT *
          FROM sys.database_role_members AS RM
          JOIN sys.database_principals AS U
            ON RM.member_principal_id = U.principal_id
          JOIN sys.database_principals AS R
            ON RM.role_principal_id = R.principal_id
          WHERE U.name = @username
            AND R.name = @rolename)
EXEC sp_droprolemember @rolename, @username;

Upvotes: 3

Andomar
Andomar

Reputation: 238096

You can store the output of the stored procedure sp_helpuser in a table variable, and query on that:

declare @groups table (
    UserName varchar(max),
    GroupName varchar(max),
    LoginName varchar(max),
    RefDBName varchar(max),
    DefSchemaName varchar(max),
    UserId int,
    SID varbinary(max)
)   

insert into @groups exec sp_helpuser 'TheUser'

if exists (select * from @groups where GroupName = 'TheRole')
    begin
    print 'Removing user from role...'
    exec sp_droprolemember 'TheRole', 'TheUser'
    end

However, it doesn't hurt to just execute sp_droprolemember regardless of whether the user is in the role.

Upvotes: 2

Related Questions