检查角色是否由DB中的特定用户组成？ [英] 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.

例如。
如果用户存在于角色
BEGIN
从角色删除用户
END

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

推荐答案

这是我做的：

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;

这篇关于检查角色是否由DB中的特定用户组成？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！