Check if role consists of particular user in DB?

Question

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

Answer 1

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.

Answer 2

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;