So, as you can tell from the lack of posts over the past few months, I have been more than just busy. Between moving into a new house, moving my son into a college dorm, several SQL Saturdays, and just general work items, it has been extremely hectic. But, I am back now, and my plan is to post lots more items for your enjoyment.
If you’ll remember from last time in our current series, we were talking about things to review when doing a Security Audit.
One of the many things to worry about is the number of System Administrators (SysAdmins) on an instance. The number of SysAdmins on any instance should be low – single digits, if possible. To find all of the SysAdmins on your instance, just run the following code:
SELECT CONVERT (NVARCHAR(40), name) AS 'SysAdmin '
FROM sys.server_principals
WHERE IS_SRVROLEMEMBER('sysadmin', name) = 1
ORDER BY 1
Once this is listed, you should go through the list with the business to make sure of several things, such as:
- If the user still works at the business.
- If the user still needs access to the SQL Server instance
- If the user still needs SysAdmin privileges
Now, for even more privileges, you will need to pay attention to the following results:
SELECT CONVERT(NVARCHAR(50), p.name) as 'ServerAdmins'
FROM sys.server_principals r
JOIN sys.server_role_members m ON r.principal_id = m.role_principal_id
JOIN sys.server_principals p ON p.principal_id = m.member_principal_id
WHERE r.type ='R'
and r.name='serveradmin'
The people in this list have access to everything on the server. So, please feel free to make sure that they pass all of the questions above, and then you have to make sure that they can be trusted with everything – because that’s what they have access to.
Now that I’ve probably scared everyone, please come back next time so that we can go through even more fun with Security Audits! So until then…
1 comment