Category Archives: Security
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.
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!
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?
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