How to get the IS_MEMBER equivalent for a specific user using "Windows login",

Question

I need to check which roles will be performed by a Windows Auth user. We use only Windows Auth Login to check access rights for specific users. We have some problems for members who do not have the right accessibility. I need to check whether the reason was really membership in sql or in our program itself. Thanks.

What our program looks like by running SP with multiple IFs to check if which user member is

IF IS_MEMBER('Administration') = 1 SELECT 'Admin' IF IS_MEMBER('Public') = 1 SELECT 'Public' 

sort of.

I need to check if which user has user login. I know his username, but don’t know how to verify it. I think we are using SQL SERVER 2008 or higher.

---

If I run this request using my Windows Auth Login.

 SELECT IS_MEMBER('Public') 

I got the result = 1, which means that I am under Roles \ Database Roles \ Public

we have certain roles in the database, but I don’t see which group I work with until I have fulfilled the above request and saw the result as “1”.

I needed to do the same and check if a certain user (using the Windows Auth login) is not in which group he is. He is just an ordinary user, so asking him to fulfill my request is almost impossible. thanks.

Answer 1

For server roles (which you think came from your original question):

 SELECT r.name FROM sys.server_role_members AS m INNER JOIN sys.server_principals AS l ON m.member_principal_id = l.principal_id INNER JOIN sys.server_principals AS r ON m.role_principal_id = r.principal_id WHERE l.name = N'DOMAIN\Username'; -----------------^^^^^^^^^^^^^^^ replace this part with your domain\user 

For database roles:

 SELECT r.name FROM sys.database_role_members AS m INNER JOIN sys.database_principals AS dp ON m.member_principal_id = dp.principal_id INNER JOIN sys.server_principals AS l ON dp.[sid] = l.[sid] INNER JOIN sys.database_principals AS r ON m.role_principal_id = r.principal_id WHERE l.name = N'DOMAIN\Username'; -----------------^^^^^^^^^^^^^^^ replace this part with your domain\user