Posted by: Colin Smith
Audit, Powershell, Seperation of Roles
I am working on a Powershell script that will audit my SQL Servers Logins and tell me who is a member of the sysadmin role. I think that this is a good idea for any DBA. I know that I do not want to have very many people with this type of access to data that I am in charge of protecting. This script will enumerate a list of users and groups with sysadmin role and then for each group it will list the members of the group. I am doing this because the Local Admin group has the sysadmin role on all of my servers. I have a vested interest in knowing who is a part of that group. I have already found some dev application service accounts that are a member of the local admin group on one of my servers. I do not like this and I do not like applications that require it. If an application does require sysadmin role then I am a big fan of giving that appllication its own instance of SQL Server.
Another reason that I am doing this is to show how many people have access that they do not need. I will use this as ammunition to remove the local admin group from the sysadmin role. I would like to have very tight control over that role and not turn over the keys to the SQL kingdom to anyone or anything that I do not deem worthy of it. Now that is not to say that I think that the people in that group are not technically sound, I just do not want more hands in the cookie jar than are necessary.