Hello Geeks,
Welcome to the new series of seven wonders of SQL Server. These are not the wonders which make you say Aww. These are the ones which make you really wonder why. I will be covering the seven of such jaw dropping situations where you wonder what is happening and few bizarre reasons why it is the way it is.
Last week we have seen how a maintenance plan setup can go bad. Today let us look at another scenario which will make you wonder, why?
One famous question I have heard when I was new to the IT world and the DBA role, how do I restrict a DBA from having an Administrator role. I had a question which I did not put through for obvious reasons. Why do you want to call him a Database ADMIN when you don’t want to give him ADMIN privileges? The intention of the question raiser is simple. I need transparency (OR I do not trust my DBA). I see how important his question was once I have seen non DBAs and less experienced DBAs having admin access.
One fine day, at one of the companies I worked for, the dev instance was restarted. Upon digging deeper, we found the rat in the hole was a developer with admin access. The reasons for his admin access is old dated and the effects are not justifiable. The argument is always two sided. But, if the decision maker is pro developer, a DBA is helpless. One such incident helps a DBA to prove his word right about limiting access. Moment of accomplishment when you reduce the access to at least a db_owner (I know you want more, but this is just the beginning).
One more argument is to add local administrators to the SQL sys admin role. Readers reading this blog can go back and check your environment. I bet in majority of the cases this will be true in your production servers.
I will be brave here and take the name of our nightmare in most cases. If you don’t know how to deal with them, I wish you had a different job. The windows admins/ server admins. They can justify that they should be local administrators on the box where SQL is installed. But, when it comes to SQL, it is my territory. I will make sure that the local admins are removed with an initial argument than to have a dialogue with them every instance. Again, all the best when you deal with this approach.
You can use my script of SQL Server Security Audit to pull the logins and their database level permissions. To summarize, a DBA is the ultimate decision maker to decide who gets what access to the database servers. If you lack the authority, be prepared to deal with the heads that will hold you accountable for all databases under your nose.
Happy learning,
Manu
Like us on FaceBook | Join the fastest growing SQL Server group on FaceBook |
Follow me on Twitter | Follow me on FaceBook
