List Sysadmins – SQL Server security

02 December,2013 by Jack Vamvas

The dangerous life of the sql server sysadmin.

 There are many risks associated with maintaining the sysadmin privilege on a logon. As well as exposing pretty much everything on the database server to the logon , there are some potential risks that require immediate attention, such as :

1)     Ability to use xp_cmdshell

2)     Ability to use sp_configure

3)     Drop database

4)     Fail most independent audits

5)     Losing integrity of data

6)     The list goes on.....

You’re main task is 1) to ensure a logon cannot break the database server and then 2) if they do break the server – be able to identify who broke the server.

For the first point, aim to apply the concept of least privilege – in other words, understand the requirements the user needs and only apply enough privileges for them to complete the task

The second point , if you must have elevated privileges ,aim to use Windows authentication. At least you can trace the access path.

To list the logons with sysadmin privileges use this query. The query does not return the “sa” logon account

How to fix this problem

1)     DON’t just start changing privileges. Firstly, identify the logon with sysadmin and contact the users. There may be a legitimate reason. 

2)     Spend more time at the database server setup stage – working with the application owners to grant appropriate privileges. Apply a rigorous SQL Server - Security Risk Analysis and database security process before setting up a new server and regular reviews

3)     Complete regular SQL Server Security Audits

										
SELECT
p.name AS [Name]
FROM
sys.server_principals r
INNER JOIN sys.server_role_members m ON r.principal_id = m.role_principal_id
INNER JOIN sys.server_principals p ON 
p.principal_id = m.member_principal_id 
WHERE r.type = 'R' and r.name = N'sysadmin' 	
AND p.name <> SUSER_SNAME(0x01)							
					

Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on List Sysadmins – SQL Server security


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer