This post is part of a series based on my presentation The Top Ten Skills You Need for SQL Server. This post is part of Skill #3 – Setup Security.
I wrote about the basic security model for SQL Server, recently and wanted to now expand on the practical aspects of how you setup security. Let’s start with logins and creating them.
In the current versions of SQL Server, which includes SQL Server 2008 R2 and below, a login is the way in which you connect to an instance of SQL Server. Things may change in SQL Server 11 with contained databases, but I think this will still apply in many situations.
Logins are defined at the instance level, and in Management Studio, you can right click the Logins folder and select New Login to create one.
This will create a dialog like this one:
There are lots of choices here, but really there are only a few decisions that we make for most logins. You might use the other options, but these are the basics for 80% of the cases, following the 80/20 rule of this series.
The first thing is the login name. This can be a user or group in Active Directory, and as you can see in the next image, if you don’t know the exact name, there is a search button. This is the standard AD search dialog.
There is a radio button below the name, which defaults to Windows Authentication. That’s for AD accounts, and is the recommended default. You can also choose SQL authentication, which means that a password is required and the administrator must set it.
If you choose SQL authentication, and are on Windows 2003 or later (Vista/XP as well) that allow you to set password policy. As a note, leave these checked unless you have a great reason not to. For most logins you should not know the user’s password and it ought to conform to the policies.
The rest of this page is advanced stuff that isn’t often needed. The only thing that you should look over is the default database. For normal users, make sure this is a database the person will have access to. For administrators, leave it at master.
Next we look at the server roles page:
These are roles, or groups, with permissions for the instance. They don’t necessarily give a person access to a database, but many of them could allow someone to gain access, so for most users, leave this blank. For administrators, give them just what they need.
The user mapping is next, and this is where you can have the dialog create a user in the database and grant access.
Most users will need access to a specific database, usually the one you chose as their default database. If you select a database checkbox, the user will be created with the same name as the login by default. Leave this alone, it’s a good policy.
Once you select the user, you also can add a database role at the bottom. Everyone is a member of public, and you should have a database role you’ve created for permissions that you can assign to them. I dislike giving regular users any of the fixed database roles like db_datareader. They are too global in permissions and have caused me confusion later on.
Create your own role and assign permissions.
Next is the explicit securables tab. Don’t use this unless you know what it means. I never use it, and most of the time you shouldn’t. Leave it along until you learn why, and more importantly, why not to use it.
That last tab isn’t one you normally need, but you might come here if a user has locked themselves out.
Leave these defaults alone unless you need to disable the user or prevent them from accessing this database for some reason. You can switch the radio buttons. If the user is locked out, the bottom checkbox will be selected and you can uncheck it.
That’s the basics of creating a user in SSMS. For the most part, stick with defaults and keep your security simple, and restrictive. Don’t grant more rights than you need to.
Voice of the DBA 