The Multifunctioning DBA

Feb 25 2010   10:00AM GMT

Powershell Sysadmin Role Audit Script



Posted by: Colin Smith
Tags:
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.

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: