I am new to SQL database and besides running basic queries I don’t know much. Trying to figure out how to get users with
Can anyone guide me through this, will really appreciate the help.
The simplest scenario (a user explicitly granted
sysadmin directly) is covered by the following query:
SELECT p.name FROM sys.server_principals AS p INNER JOIN sys.server_role_members AS m ON p.principal_id = m.member_principal_id WHERE m.role_principal_id = 3;
But then you may also have users who are members of a Windows group (which SQL Server won’t know about) and that group has been granted
sysadmin. To get all of those users you may need to run some queries against Active Directory (or ask someone who can).
Nobody suggested IS_SRVROLEMEMBER, so here’s a third option. Same aspects applies as what Aaron mentioned.
SELECT * FROM sys.server_principals AS p WHERE IS_SRVROLEMEMBER('sysadmin', p.name) = 1
Following query will give the logins and windows groups with sa permissions on a server.
SELECT name,isntname, isntgroup, isntuser FROM master.sys.syslogins WHERE sysadmin = 1 ORDER BY isntgroup, isntname, isntuser
Result set points out the login as windows groups or windows logins as well.