Monthly Archives: November 2013

What Exactly Is This Sysadmin You Speak Of?

SecuritySysadmin; 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:

sp_helpsrvrolemember

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

Results

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.

Advertisements

Making A Difference

MakeADifferenceA father was awoken by his wife to check on their son who was asleep but making noises that they could hear through his room monitor. The father, half a sleep and thinking his son was dreaming, wondered into his room and called out to his son only to find that his son wouldn’t answer him. The dad stumbled over to his son’s bed to find that his eyes were rolled back in his head and he was stiff as a board. Panic set in, the worst scenario for the parents was coming true. The son, who had type 1 diabetes, had gone into a diabetic shock – his blood sugar had dipped to low while sleeping. The father feeling for a pulse; scooped the son up and rushed him down stairs to apply an emergency Glucagon shot while the wife called for an ambulance.

Why am I sharing this you may ask? Simple, the father of that boy is me and from that night on he has been my hero which leads me into this article…….

I was fortunate and blessed to attend my first PASS SUMMIT in 2011. After getting passed the complete awesomeness and the many renowned speakers I found myself wanting to ask questions and kicking myself in the tail for not asking them. Come on, let’s be honest, you’ve been there with various things in your life. You don’t want to ask questions because you don’t want to feel not as smart, or you see someone who has years’ experience and think why would they want to waste time on what I think. I know I felt that same thing and then it all kind of just clicked in my head.

I got to thinking about my boy while at that PASS Summit and the inspiration he had given me that night, then sitting in a session and looking over and seeing Brent Ozar in the class actually learning (floored me; in my eyes this guy knows everything SQL related), or Paul Randal sitting in on a session a few seats away, or asking advice from Chris Shaw on a presentation he did on Utility Databases. I found out a few things at the Summit about myself that otherwise I would have stayed in my shell.

  • The people that you look up to in the industry are 9 times out of 10 the most down to earth people you would ever meet and are willing to offer advice if you ask.
  • Step out of that comfort zone; this blog was started based on attending the PASS Summit and thinking if I can help one person along my SQL journey as a DBA then it is all worth-while.
  • Speaking – never thought I’d do it but found out I truly enjoy it and helps me interact with a lot of faces and people.
  • Learn from the SQL Community as a whole; have you been to any forums, blogs on a regular schedule?
  • Not one question is a dumb question; everyone learns so go ahead and ask that question  you are hesitant about.
  • Hard work – it does pay off. Don’t short cut anything – dig in, dive in, and give it all you got.

If you have thought about stepping out and starting your own technical blog – DO IT

If you have thought about going up to someone and asking them for advice – DO IT

If you are at a conference and you have a question but the speaker is what you call a Master Jedi in our industry – DO IT

It takes one to make a difference in someone’s career, it takes one to make a difference to someone who might not yet be comfortable in asking the question to solve the problem, it takes one to encourage the person just starting out to get the most out of their potential.

For those of us who have been in the SQL industry for years; when is the last time we put away our ego’s and  helped the one starting out? Or maybe we have a wealth of  knowledge and need to step out and be a voice in the SQL Community.

It takes one ~ will you be that one?