none
admin level sql authentication accounts RRS feed

  • 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?
    Wednesday, August 14, 2013 8:21 AM

Answers

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

    Tibor Karaszi, SQL Server MVP | web | blog

    Wednesday, August 14, 2013 10:51 AM
    Moderator
  • This script will list all logins that have the sysadmin role

    sp_helpsrvrolemember 'sysadmin'

    Wednesday, August 14, 2013 1:37 PM
  • 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

    Wednesday, August 14, 2013 3:34 PM
    Moderator

All replies

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

    Tibor Karaszi, SQL Server MVP | web | blog

    Wednesday, August 14, 2013 10:51 AM
    Moderator
  • This script will list all logins that have the sysadmin role

    sp_helpsrvrolemember 'sysadmin'

    Wednesday, August 14, 2013 1:37 PM
  • 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.

    Wednesday, August 14, 2013 2:52 PM
  • 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

    Wednesday, August 14, 2013 3:34 PM
    Moderator