如何查询当前用户的角色 [英] How to query current user's roles

问题描述

我正在寻找一个 select 语句，它将检索我当前连接的所有数据库角色的列表.

I'm looking for a select statement which will retrieve a list of all database roles for my current connection.

我想创建一个视图，将所有角色返回给客户端软件，以便软件可以根据角色调整其用户界面(例如显示/隐藏菜单项等)

I want to create a view which will return all roles to a client software, such that the software can adjust its user interface according to the roles (e.g show/hide menu entries etc)

推荐答案

您不应使用已弃用的向后兼容性视图 (在此页面中搜索sysusers，例如).相反，您应该使用 sys.database_principals 和 sys.database_role_members.请记住，当前连接可能已被授予数据库范围之外的访问权限(例如，如果用户恰好是 sysadmin，则这些将返回空结果，在这种情况下，他们不需要被明确授予角色成员资格或特定权限).此外，对于在角色范围之外明确分配的权限，这将覆盖角色提供的权限，您应该另外检查 sys.database_permissions.这是一个您可以查看的独立示例(只要您还没有名为 blatfarA 的登录名或名为 floob 的数据库).

You should not be using deprecated backward compatibility views (search this page for sysusers, for example). Instead, you should be using sys.database_principals and sys.database_role_members. Keep in mind that the current connection may have been granted access outside of the scope of the database (e.g. these will return empty results if the user happens to be a sysadmin in which case they don't need to be explicitly granted role membership or specific permissions). Also for permissions assigned explicitly outside the scope of a role, which will override those provided by the role, you should additionally check sys.database_permissions. Here is a self-contained example you can check out (as long as you don't already have a login named blatfarA or a database called floob).

CREATE LOGIN blatfarA WITH PASSWORD = 'foo', CHECK_POLICY = OFF;
GO
CREATE DATABASE floob;
GO
USE floob;
GO
CREATE USER blatfarB FROM LOGIN [blatfarA] WITH DEFAULT_SCHEMA = dbo;
GO
GRANT SELECT, UPDATE ON SCHEMA::dbo TO blatfarB;
DENY INSERT, EXECUTE ON SCHEMA::dbo TO blatfarB;
GO
EXEC sp_addrolemember N'db_datareader', N'blatfarB'
GO

测试:

EXECUTE AS LOGIN = N'blatfarA';
GO

DECLARE @login NVARCHAR(256), @user NVARCHAR(256);

SELECT @login = login_name FROM sys.dm_exec_sessions WHERE session_id = @@SPID;

SELECT @user = d.name
  FROM sys.database_principals AS d
  INNER JOIN sys.server_principals AS s
  ON d.sid = s.sid
  WHERE s.name = @login;

SELECT u.name, r.name
  FROM sys.database_role_members AS m
  INNER JOIN sys.database_principals AS r
  ON m.role_principal_id = r.principal_id
  INNER JOIN sys.database_principals AS u
  ON u.principal_id = m.member_principal_id
  WHERE u.name = @user;

SELECT class_desc, major_id, permission_name, state_desc
  FROM sys.database_permissions
  WHERE grantee_principal_id = USER_ID(@user);

GO
REVERT;

结果:

name      name
--------  -------------
blatfarB  db_datareader

class_desc  major_id  permission_name  state_desc
----------  --------  ---------------  ----------
DATABASE    0         CONNECT          GRANT
SCHEMA      1         INSERT           DENY
SCHEMA      1         EXECUTE          DENY
SCHEMA      1         SELECT           GRANT
SCHEMA      1         UPDATE           GRANT

清理:

USE master;
GO
ALTER DATABASE floob SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE floob;
GO
DROP LOGIN blatfarA;
GO

这篇关于如何查询当前用户的角色的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！