admin level sql authentication accounts

Question

please excuse my ignorance (not a DBA or MSSQL tech), but I have a task to audit several MSSQL instances (ranging from MSSQL 2000-2008), and we need to document all SQL authentication accouns and highlight the "admin" type accounts, i.e. the higher risk ones. What determines if a SQL authentication is an admin type account, or a lower priveleges account? i know SA is an admin account , but what determines its an admin account, is it granted some specific role?

Answer 1

You can who has the sysadmin role, using the IS_SRVROLEMEMBER() function.

---

Tibor Karaszi, SQL Server MVP | web | blog

Answer 2

This script will list all logins that have the sysadmin role

sp_helpsrvrolemember 'sysadmin'

Answer 3

just i am adding more to Tibork and SQL24 ans

pls refer here, you ill get some clear idea.

http://technet.microsoft.com/en-us/library/ms176015.aspx

http://technet.microsoft.com/en-us/library/ms188772.aspx

---

Thanks - SelvaKumarSubramaniam.Please MARK AS ANSWER, if my answer is useful to U.

Answer 4

Also check for members of the securityadmin role. From Books Online Permissions of Fixed Server Roles (Database Engine) http://msdn.microsoft.com/en-us/library/ms175892(v=SQL.105).aspx: "The ability to grant access to the Database Engine and to configure user permissions allows the security admin to assign most server permissions. The securityadmin role should be treated as equivalent to the sysadmin role."

You probably also want to check for logins that have the CONTROL SERVER permission. It's not quite the same as a member of the sysadmin fixed server role, but it is very similar. Use:

SELECT prin.name, perm.*
FROM sys.server_principals AS prin
JOIN sys.server_permissions AS perm
ON prin.principal_id = perm.grantee_principal_id
WHERE permission_name = 'CONTROL SERVER';

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty