How to implement query user role script by sqlserver

This article mainly introduces how to achieve sqlserver query user role script, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let Xiaobian take you to understand.

Query user roles:

Select UserName = u.name _ DbRole = g.name

From sys.database_principals u, sys.database_principals g, sys.database_role_members m

Where g.principal_id = m.role_principal_id

And u.principal_id = m.member_principal_id

Order by 1, 2

Go

Select DbRole = g.name, MemberName = u.name, MemberSID = u.sid

From sys.database_principals u, sys.database_principals g, sys.database_role_members m

Where g.principal_id = m.role_principal_id

And u.principal_id = m.member_principal_id

Order by 1, 2

Go

Select SrvRole = g.name, MemberName = u.name, MemberSID = u.sid

From sys.server_principals u, sys.server_principals g, sys.server_role_members m

Where g.principal_id = m.role_principal_id

And u.principal_id = m.member_principal_id

Order by 1, 2

Go

Look at the server role:

Select user name = u.name, administrator privilege = g.name, whether or not u. IsobilledMemberSID = u.sid is in use

From sys.server_principals u, sys.server_principals g, sys.server_role_members m

Where g.principal_id = m.role_principal_id

And u.principal_id = m.member_principal_id

And g.name = 'sysadmin'

Order by 1, 2

Go

Thank you for reading this article carefully. I hope the article "how to query user role script" shared by the editor will be helpful to everyone. At the same time, I also hope that you will support and follow the industry information channel. More related knowledge is waiting for you to learn!