Sysadmin; seems important doesn’t?

What exactly is this sysadmin you speak of?

SQL is provided with certain fixed server roles out of the box to help  manage permissions. This doesn’t mean you should stop with just these roles; how locked down you need to be is often times dependent on the place or environment you are in although I’m a very big advocate of ensuring your SQL servers have the proper and adequate security necessary and in place.

While there are nine fixed server roles out of the gate I only want to speak on one and that is the sysadmin role.

By simple definition the sysadmin role contains members who can do anything on the SQL server itself. Now that you know what members in this role can do let me ask you something else; on your servers do you know who are members of this role? If the answer is no then I suggest you take a moment and dive into your servers to find this information out as you potentially have a door wide open that needs to be shut.

This particular role can do anything it wants to the SQL Server, I said this particular role can do ANYTHING, it wants to the SQL Server. This role needs to be carefully controlled and monitored for it can bypass security checks; it is by far the most powerful role so get familiar with it ~ quickly.

Is it a daunting task to discover this information? The answer to that is no it is not and I’ll show you a quick way that I utilize to discover who are my members in this role.

Microsoft has provided additional views, functions, and commands that are there to help the everyday data professional. Some of these options are known, others are not, and some are just not known by people as they never have had to run across utilizing such before. Just like fixed server roles there are about ten or eleven that I can think of off hand that are good to know, but again I only want to focus on one –sp_helpsrvrolemember.

This system stored procedure is a gem as it will provide information on specific fixed roles that you want to inquire about; or if left null will return information on all fixed roles. Let’s take a look…..

First, the syntax…..you ready for it it is quite lengthy:

That’s it, I execute this in the master database; the result sets are quite simple:

What the result set provides you is a quick and easy look at who has sysadmin privileges on the SQL server; as you can see one that would raise an eyebrow immediately is the Test User log in. Why would this user need sysadmin privileges?

Conclusion

For a more in depth look at the fixed server roles and working with them you can visit the Microsoft link below:

http://technet.microsoft.com/en-us/library/ms188659.aspx

If you are new to SQL server or if you just haven’t ever thought about seeing just who holds the keys to the SQL kingdom it would behoove you to check it out. Utilize what has been already provided and start taking control of your environment. In the end it is our responsibility and duty to ensure security of our data is constantly being met.

There are many, many different twists and turns to explore; this is just the tip of the iceberg of what has been provided. I recently was approached just how do you obtain such information quickly; well this is it. Short, sweet, and too the point.

I’ve often found on machines I’ve had to hop on or take over this is one aspect that has always interested me. When I ask the question, “Do you know who is sysadmin on your servers” , what would your answer be?

Okay – it’s game time, be a play maker, and change the status quo. Take over your environments and ensure proper standards are in place and best practices are being met.