Monthly Archives: January 2012
Orphaned Windows Users….Annie?
A caveat when working with Windows domain users in your SQL Server is the possibility of orphaned users. When a user is removed from AD, they are not automatically removed from SQL Server. This is an orphaned user as far as SQL Server is concerned. Thus, no access will be provided to this account even if the AD account is recreated because it will have a new SID that will not match the existing item stored in SQL Server. You can run the sp_validatelogins stored procedure to display if any orphaned users are in the database.
To resolve this problem you will need to remove the user using DROP LOGIN ‘AD\Annie’. Then you will need to revoke the user’s server access using EXEC sp_revokelogin ‘AD\Annie’ to remove the user’s access to the server.
Enjoy!
Windows Logins Basics
As most people would agree, Windows Authentication is the preferred authentication method in SQL Server as it provides consistency with your Windows or AD administration and it allows you to use groups for ease of administration if your users are organized into groups by security level and function.
To create a login in T-SQL use the following code:
CREATE LOGIN ‘AD\Ed’
To remove a login using T-SQL, use the following code:
DROP LOGIN ‘AD\Ed’
To deny a login using T-SQL, use the following code (NOTE: if the user or group does not exist in SQL Server, this command will add them first):
DENY CONNECT ‘AD\Ed’
This is a good foundation for a couple of other posts that I am working on for this week. Enjoy!
Sunday Funday
Sunday is a great day for SQL funday, so every Sunday I will post some fun, SQL related links for your enjoyment….
Today’s installment is called Minesweeper in T-SQL! This is a clever and fun T-SQL script, so check it out!
Enjoy this #SQLAwesomeness!
Saturday SQL Schoolhouse
In our fourth installment of the Saturday SQL Schoolhouse, I wanted to share with you a neat little beginner’s resource for learning SQL Server 2008.
The site is called Quackit.com. It is a simple website that is aimed primarily for beginners. Enjoy!
Logon Triggers aka How To Lock That Annoying User Out During Lunch
Earlier this week, I chose a few SQL Server 2008 topics for our #SQLHelp MCITP study group to discuss here on the blog. Thus, today I will discuss logon triggers, one of the new features available in SQL Server 2008 (well actually if I remember correctly they were snuck in 2005 SP2…shhhh). Logon triggers are a special form of trigger that the database administrator can use in order to monitor logon events.
Logon triggers can be used for the following purposes:
- To audit and control server sessions (such as tracking logon activity)
- Limiting the number of sessions allowed for a particular logon
- Restricting logons to SQL Server (similar to AD snap-in that lets you set the hours that a user may logon)
Here is an example from books online showing how to set up a logon trigger that limits the number of concurrent sessions for a particular logon, logon_test in this case. This example, in particular, will limit the concurrent sessions to three. You must create this logon in order to test this code, or change the logon name to match your requirements.
CREATE TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS ‘logon_test’
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= ‘logon_test’ AND
(SELECT COUNT(*) FROM sys.dm_exec_sessions
WHERE is_user_process = 1 AND
original_login_name = ‘logon_test’) > 3
ROLLBACK;
END;
Here is some sample code for restricting the user based on logon time (between 12Pm and 1pm in this example for all users except the SA\SQLAdministrator):
CREATE TRIGGER Restrict_Logon_HoursON ALL SERVERFOR LOGONASBEGINIF (DATEPART(HOUR, GETDATE()) BETWEEN 12 AND 13) AND(SUSER_SNAME() != ‘AD\SQLAdministrator’)BEGINROLLBACK;
END;END;
Once you have created logon triggers, you can view them by a simple query to the sys.server_triggers metadata.
Zest for Learning
In today’s competitive marketplace, we must have a zest for learning to remain relevant and marketable. While I am excited about the release of SQL Server 2012, its launch means that my 2008 MCTS has just become a little less marketable. The certification itself does not expire but it’s usefulness is definitely dated. I know many other DBAs have been testing and learning Denali for some time now. Time to pick up the slack.
Since my agency is in the process of implementing 2008R2 boxes replacing our 2005 boxes, we are in the forefront of available technology today. However, in a few months we will once again be one version behind. I can live with that, but my learning must continue. I have been planning on setting up a 2012 lab where I can be ready for the day that we migrate these new boxes.
The reality is that we must keep learning, we must keep moving, and we must remain relevant. Keep up the good work and enjoy!
Goals Updated
We are almost a month into the new year and so far I am on track to complete my stated goals for the year. However, I know one item that will not be completed due to a constraint by VMWare to end testing on the VCP certification this month. Normally you have one year from the date of attendance in the VMWare course to sit for the test before you have to take the class again. That date for me was in March and given my current projects I will not be able to be prepared to sit for that exam by the end of the month. This is not a big deal because VMWare is not my primary career focus, it was just something I thought I could add to my resume.
It appears that I am going to be able to take Paul Randal and Kimberly Tripp’s Immersion training at the end of February, which is an incredible opportunity. Hopefully I will get to meet some other people in the #SQLFamily there.
The one goal that I am proudest of thus far is this blog. I thought it would be difficult to find something to write about everyday, however I have been able to keep writing everyday. It is getting easier every day. I am also trying to do one technical article each week. I will post updates as we go on this journey together. Enjoy!
Security or Productivity?
Today I would like to discuss security, the necessary evil. Security is a great thing, when it is keeping other people out of my stuff or when it keeps people from bringing down my SQL Servers. I love that security, and most of you do too. No DBA enjoys SQL injections or developers with accidental db_owner access! We can all agree on those principles. Can I get an Amen, sorry I felt a little like a small town Southern Baptist preacher extolling the evils of sin.
The sin I am referring to is that of your local Security Administrator. You know the guy, he is the one with the long list of security acronyms at the end of his name on his e-mail signature that you have never heard of. In my world he is the satan caricature with the little red pitch fork and bifurcated tongue. He is the sadist who makes you use 25 characters with a mixture of numbers, a mixture of cases, and at least one symbol without any repeating characters or dictionary words rapidly accelerating your carpal tunnel syndrome fifty times a day. He is also the sadist who makes you change your password on regular intervals ensuring that you never remember it without writing it down and then he turns you in for keeping a handwritten list of passwords at your desk. He is also the guy that will not let you use single sign-on or assume that you are who you login as in a TRUSTED authentication. At this point in my career, I am confident that he is purely sadistic!
One of the first lessons I ever learned in the server world when learning Windows NT 3.51 was that security was essential. However, there is a simple axiom that defines everything we do in this arena: the more secure a system is the less productive it will be. In other words, the more you lock down the system, the less you will get out of it. We can thank the script kiddies as well as the black hat hackers for breeding this devil into our workplaces. However, there comes a time when every DBA must stand up and say no more! Your silly compliance regulations are costing me in productivity. It is your job to secure the systems and not strap everyone down to where they cannot move! In my office, I cannot install software onto my own PC, however I am responsible for almost 200 servers. There is something wrong with that picture. I am starting to think that the axiom is now: Security or Productivity…you cannot have them both. What about your environment?
Enjoy!
Excitement or Business As Usual?
After a hectic Monday morning, I sat back and reflected upon the events that occurred. For most of my career, I would dread coming to work on Monday mornings, and I loathed the stressful work environments created in the aftermath. At this point in my career, I thrive on the chaos that is called Monday. Most days are business as usual, especially if your plan is to be as proactive as possible, however Mondays still provide excitement. Mondays provide a challenge. Mondays are a raging tower of excitement. Well, maybe I am getting a little carried away, but you get the picture. How do you tackle the beast that is Monday?
Enjoy!
Sunday Funday
Sunday is a great day for SQL funday, so every Sunday I will post some fun, SQL related links for your enjoyment….
Today’s installment is called Having fun with DOT while writing TSQL code! It presents some interesting scenarios, check it out!
Enjoy this #SQLAwesomeness!
SQL Swampland 