How to find loginname, database username, or roles of sqlserver domain user who doesn't have their own login?

Question

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".

For example, this query returns "MYDOMAIN\username"

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

And this query returns 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?

Answer 1

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

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 :)

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.:

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.