Tag Archives: SQLServer

What Exactly Is This Sysadmin You Speak Of?

Leave a reply

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.

Adversity – How Do You Handle It?

Leave a reply

Adversity Adversity by definition is a difficult situation or condition misfortune or tragedy. When that time comes; how do you handle it?

If replication breaks causing the business to not get the data they need in a timely fashion and you are the lone conductor driving the train down the tracks and everyone is standing at your desk, how do you handle it? I

f your backups failed from the previous night and for some reason your notification of failure didn’t reach you and you had no knowledge of the situation until days later, how do you handle it?

Someone calls and said they can’t access their systems and you find out something is causing tempdb to fill up, how do you handle it?

Better yet, you find out that a certification you were working toward had been retired (MCM), how do you handle it?

Response

I can only speak for myself and no one else, I fell into the same category a lot of people across the nation did of working toward the MCM certification goal. I mean let’s face it, last I checked a very small percentage obtained this goal and being the competitive person I am I wanted to reach that goal. Not for anyone else but for myself and the standards I set for my own self.

At first I was upset and like so many others not so much toward the retiring of the program but how it was delivered by Microsoft. I purposefully waited to construct a blog post as I didn’t want to let emotions get in the way of potential views toward the topic.

Adversity has now hit for a lot of people, for me in particular I choose not to dwell on it but wait to see what will be offered next by Microsoft, restructure my goals, and move forward. I am a bit biased but I get to work with some of the best technology day in and day out and am in constantly learning mode. Some of the things that we can control are the way we handle adversity when it rears its ugly head, the attitude to how we conduct ourselves through the adversity, and the character building we can learn from it.

Am I happy about the retirement, no I wasn’t. At the end of the day though I do have a lot to be thankful for and looking back on my studying, lab testing, hours of working toward the certification it allowed me to push myself to learn a lot more than what I did the previous day and the day before that. I’m eager to see where the next chapter leads in the stepping stone of my learning.

Adversity – how will you handle it? Each of us are different; doesn’t make one person better than the next by how we handle it, but I do encourage others to push through adversity when it comes. You’ll be a stronger person in doing so.

Where Do I Start – The Journey?

Leave a reply

journey

I’ve recently been approached by numerous people at work, on boards, forums, twitter, etc. regarding different ways to learn and improve one’s skill set.

The Forums

I find myself on forums more so than normal perusing through questions, providing feedback where applicable and so on. Some of the ones I traffic often are:

  1. SQL Server Central
  1. The SQL Brit’s Forum

I think forums are a great avenue to see what others are sharing on topics that I might have gone through, are going through, or will go through.

Bloggers

I enjoy reading/following bloggers of who I consider to be at “the top” of the SQL game. I will not list them all out here but if you look to the right you under DBA Blogs you will find additional links of blogs I follow. Over my decade in dealing with SQL I have tried to model and pick up some of the knowledge that they share on a regular basis. Take time to hit some of their sites and remember if you do reach out to one of them I always say be respectful; a lot of their time is providing free knowledge to the community which is what helps make the community grow and be better; be sure to thank them for all they do.

Email Subscriptions

My days normally consist of several hundred emails daily but I try to take a certain part of my day and read the emails I subscribe to; I really enjoy MSSQLTips, SWUGG, the live burn feeds I receive from the bloggers I follow, and several more. Getting involved and subscribing to some of the top tier SQL sites provides another avenue for learning opportunities, networking, etc.

Books

Really depends on what SQL Server versions you are running right now. A lot of people have been asking me about 2008 R2 and for that I liked the DBA Cookbook; felt it had some good stuff in there. Red Gate puts out a lot of good stuff along with the SQL Deep Dive editions.

Training Sites

Some ones that I’ve used or recommended in the past are:

  • Pluralsight – I’m a big Paul Randal and Glenn Berry fan so when SQLSkills.com joined it was a no brainer for me.
  • SQL Course
  • PASS (take advantage of the local chapters and virtual chapters)

Closing

I read an article this week by SQL Brit (John Sansom) regarding “Overconfidence – How it Almost Cost Me and What You Can Do to Avoid It”. After reading it I felt like it was one of the better ones I’ve read in the past month. It put things into perspective in a sense that while I’m very confident in my abilities you can never become “lax”. I am a believer in learning everyday and I will continue to hone in on my skillset; at the same time it is imperative that we do not just “assume” or “take for granted” the regular routines we do on a daily basis.

These are some of the things (not all) I like to dig into on a regular basis and I’m sure you have your own; I’d love to hear what others utilize if you have time drop me a line and I’ll share it out on the post so other community members who pass by might be able to gain something from it.