如何查找没有自己登录的sqlserver域用户的登录名，数据库用户名或角色？

I have created a login and database user called "MYDOMAIN\Domain Users". I need to find what roles a logged on domain user has but all the calls to get the current user return the domain username eg. "MYDOMAIN\username" not the database username eg. "MYDOMAIN\Domain Users".

我创建了一个名为“MYDOMAIN \ Domain Users”的登录和数据库用户。我需要找到登录的域用户具有的角色，但是获取当前用户的所有调用都返回域用户名，例如。 “MYDOMAIN \ username”不是数据库用户名，例如。 “MYDOMAIN \域用户”。

For example, this query returns "MYDOMAIN\username"

例如，此查询返回“MYDOMAIN \ username”

select original_login(),suser_name(), suser_sname(), system_user, session_user,  current_user, user_name()

And this query returns 0

此查询返回0

select USER_ID()

I want the username to query database_role_members is there any function that will return it or any other way I can get the current users roles?

我想用户名查询database_role_members是否有任何函数可以返回它或任何其他方式我可以获得当前用户角色？

1 个解决方案

#1

I understand that the Domain Users login is mapped into AD group?

我了解Domain Users登录映射到AD组？

You have to bear in mind that user can be in several AD groups and each of them can be mapped somehow in database which may be a bit messy. Also it means you need something with multiple results :)

你必须记住，用户可以在几个AD组中，并且每个组都可以以某种方式映射到数据库中，这可能有点混乱。这也意味着你需要有多种结果的东西:)

Try this:

尝试这个：

select * from sys.server_principals where type_desc = 'WINDOWS_GROUP' and is_member(name) = 1

I think it should grab properly all Windows Group logins that will be tied with particular users. After that you can join it for database users i.e.:

我认为它应该正确抓取将与特定用户绑定的所有Windows组登录。之后你可以加入数据库用户，

Select u.name from YourDB.sys.syslogins l
inner join YourDB.sys.sysusers u
on l.sid = u.sid
where l.loginname = ANY (select * from sys.server_principals where type_desc = 'WINDOWS_GROUP' and is_member(name) = 1)

You have to keep in mind that - all the way - you may need to handle whole sets rather then single values.

你必须记住 - 一直 - 你可能需要处理整个集合而不是单个值。

#1