Category Archives: Database Administration Tasks
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!
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.
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!
Microsoft RAP, Not Just Clever Lyrics
We scheduled a Microsoft RAP, or Risk and Health Assessment Program, with our Premier Support representative to occur this week. This is an excellent program where a Microsoft Premier Field Engineer comes on site and assesses the risk and health of your SQL servers. We installed some new hardware and began a migration process moving from 2005 to 2008R2, so we thought this would be an excellent opportunity to assess our new environment as well to look at our best practices. Additionally, one of my goals for the assessment has been to put some weight behind my best practice ideas as well as some weight in order to change some practices by our developers. This is also an excellent opportunity for some of junior staff members to talk one on one with the field engineer to discuss some issues we have been working through as well as some of our pain points.
I will blog next week about some of our more interesting findings. Enjoy!
Transaction Log Fills Up the Drive….Oh No!
Maybe your users cannot access your database, or maybe your preferred monitoring software is reporting limited or non-existent disk space on your log drive for a particular SQL Server. Maybe you get a call from a Subject Matter Expert telling you their application is wigging out, or maybe you get a call from a user or even better, from your helpdesk. We have all gotten that phone call or e-mail at one point or another. A transaction log has gone awry and wreaked havoc on your limited drive allocated to the log files. What next?
Do we know the database? We could look at the drive and find the culprit. However, we will need the logical name of the log file in order to shrink it, remember it isn’t always the same as the filename. Run the following in SQL Server Management Studio (SSMS) in order to get the log file name:
SELECT name FROM sys.database_files WHERE type_desc = ‘LOG’
At this point, we can try to shrink the database by issuing the following command:
DBCC SHRINKFILE(‘<DB log file name>’, 0)
If this does not shrink the file, then run the following command to see the log_reuse_wait_desc column (way over to the right):
SELECT * FROM sys.databases WHERE name = ‘<DB Name>’
The following values of the log_reuse_wait_desc are the most common reasons that the shrink operation has failed:
- CHECKPOINT: This means that there is no database checkpoint in the transaction logs. You should manually run the CHECKPOINT command or you could also perform a database backup.
- ACTIVE_TRANSACTION: This indicates that there is an open transaction. You can use DBCC OPENTRAN to find out what is going on (I will write another blog on this soon and link back here).
- LOG_BACKUP: This indicates that the log file has wrapped around to the beginning of the file and the free space is somewhere in the middle of the file. Run another log backup in order to move the free space to the beginning or end of the log file.
After completing one of the three afore-mentioned steps, we can now issue the SHRINKFILE command again….And all will be well with the universe. Enjoy!
SSMS Shortcuts
Below are some common SQL Server Management Studio keyboard shortcuts that I use on a daily basis to make my SQL life much more productive:
- Ctrl-N…..Open a New Query with the Existing Connection
- Ctrl-Alt-G…..Display Registered Servers
- Ctrl-Alt-T…..Display Template Explorer
- Ctrl-Alt-L…..Display Solution Explorer
- F4…..Display the Properties Window
- F8…..Display Object Explorer
- F7…..Display the Object Explorer Details Window
- Ctrl-F…..Find
- Ctrl-H…..Replace
Enjoy!
You’ve Probably Had This Request Before…
So yesterday afternoon I received a management request to provide some statistics on our SQL Servers, probably to justify our positions for the next budget year. No problem, I thought, because I know how many total servers, how many clusters, how many physical boxes and how many are virtual. I also know which ones are production, acceptance, and development and I know how much RAM, how many processors and cores, how many drives and how big they all are. I also know how many licenses we own and which ones are Enterprise versus Standard, which ones are 2008R2 and which ones are 2005.
I also have a script (thanks to Pinal Dave) to tell me pretty much everything I need to know when I run it on each of my servers. However, that was not the information they requested. Wait for it, wait for it.
They wanted to know how many records or rows we maintained on how many tables. In all of my years working with SQL Server, Progress RDBMS, MS Access, and MySQL (in order of awesomeness), I do not think I have ever had anyone ask for that level of information. The quick stat that everyone wants to know is usually how many servers and how many terabytes. In our environment, they also want to know how many dot net applications are we supporting on those database servers.
Much to my dismay, my frantic google session showed many people who had a script that was essentially similar to Pinal’s script (although most were not nearly as fabulous). So I did what every DBA should do, I sent up a distress call to the #SQLHelp Twitter tag! Lo and behold who should come to my rescue, why SirSQL ((blog|twitter), who else would you expect? Although, I was not a damsel in distress so maybe that was a poor analogy, but hey it made me giggle, so why not?
Here is his solution, which is really quite elegant. I am thinking of automating it on one of my servers so that I can have this handy in case they ask for the information again. It is inevitable that they will ask for it again when you give it to them once, unless of course I spent hours writing the script myself. Thanks again, SirSQL!
New Year House Cleaning
As I was driving in to the office today, I could not help but think of my wife’s cleaning efforts at home yesterday. She decided the holiday was a good time to remove the clutter from our kitchen cabinets and drawers. That inspired me this morning to do the same and hopefully put a smile on my storage guys’ faces, at least until I add my new clusters in a couple of weeks and requests some more terabyte LUNs.
A fresh new year is a good time to go through your SQL servers and look for ancient backups that maybe got moved to a different location and escaped the cycle of retention, prehistoric MDFs and LDFs from failed migrations or temporary restorations to different servers. I know I had MDFs and LDFs from databases that were migrated to other servers that were left on the previous server as an insurance policy during the migration. After a month, I think it is safe to remove these and clean the clutter.
This can also be a good time to review your retention policies on your back up jobs and maintenance plans to make sure you are in compliance with your department’s requirements. Let’s all clean house and start the year off right! Enjoy!
Migrating SQL Server Database Logins with sp_help_revlogin
You’ve migrated your database to a new box, with no problems. You created the login that goes with this database, and all is right in the world. However, when your user goes to connect to it they get the dreaded error 18456, login failed. But wait, I created the login, right?
Not so fast there, accidental DBA. You are going to need the help of a nifty stored procedure called sp_help_revlogin. This stored procedure, provided by Microsoft in the link below, will generate a T-SQL script that will copy the logins, their passwords and their corresponding SIDs (or security identification number, which is usually the culprit here). This is also a great utility when the database has numerous users with various security levels and passwords. Really now, who wants to write all of that down and recreate it? Even with screenshots, it can turn into a large, fat-fingering and time consuming event. Mind-numbing, I would say. Why do something manually when you can do it automagically? Just think of all those happy logins that will not be orphaned by the dark side?
Here is also a code snippet to check for orphaned logins, for your enjoyment. Enjoy!
USE <database_name>; GO sp_change_users_login @Action='Report'; GO
SQL Swampland 