Before you got started working with SQL Server, someone had to set up a user account for you. Now that you’re learning to become a database administrator you need to learn to allow others to have access to SQL Servers.

Granting a user access to a database takes three steps. First, create a login, second you configure the login as a user for one or more databases, finally, you grant that user permission to database objects.

This post walks you through these three steps.

Creating a Login

The login you create can represent the user’s Windows account, Windows group, or a login that exists only in SQL Server. Whenever possible you should use Windows Authentication. This will simplify user creation, allow for a one-stop user deactivation should you need to remove the user, and gives you one and only one place to manage their password when they forget it.

You do remember DBA Rule #1, right?

Always create a user, never give out the sa account to your users. Don’t make me slap the back of your head.

I’m going to assume the user you want to create already has

To create a login

There are two ways you can create a user . Let’s start with a user with a windows account.

CREATE LOGIN <machineName|domainName>\<UserName>
  FROM WINDOWS
  WITH DEFAULT_DATABASE = DatabaseName
GO

The computerName can be the machine running the current instance of SQL Server. If your SQL Server is on a domain, you can supply the domain name instead. The username can actually be a Windows user name or a Windows User Group that the user belongs to. FROM WINDOWS indicates that Windows will authenticate the user. The optional DEFAULT_DATABASE argument connects your user to the TestData database, unless her connection string indicates another database.

The second way you can add a user, is to create a SQL user.

CREATE LOGIN <userName>
  WITH PASSWORD = '<Password>' MUST_CHANGE
       DEFAULT_DATABASE = DatabaseName
GO

Again, the userName is any user name you want to use, password is any password you want to use. The default database will be the database the user connects to, unless otherwise specified in a command string. MUST_CHANGE is a great add-in that will force them to change their password on the first connect.

Your test user now has access to this instance of SQL Server, but does not have permission to access any database on this instance. This user does not even have access to her default database TestData until you authorize them with the CREATE USER command.

To grant your user access, switch to the database you’re trying to grant permission to, and use the CREATE USER statement to create a database user that will map to the server account you previously set up.

USE DatabaseName
GO
CREATE USER userName FOR LOGIN <machineName|domainName>\<UserName>
GO

Now your user can log in to the server, and connect to the database you named. Next time, I’ll show you how to control what this user can do within the database. If you have any questions, please send them in!