Basic steps to create a new #SQLSERVER database account

by Brian

Once or twice a year I need to create an account for a SQL Server database that utilizes database authentication.  And every time I have to do this I end up spending too much time searching for the correct steps.  So this post is to help keep me from forgetting what to do for the rest of my career.

In the example below, the database we want to create an account for is [TestDatabase].  The login that will be used for authentication is DbLoginAcct.  And the user that will grant the login access to [TestDatabase] and the [dbo] schema is DbUserAcct.

USE [TestDatabase]
CREATE LOGIN [DbLoginAcct] WITH PASSWORD=N'test', DEFAULT_DATABASE=[TestDatabase]
GO
CREATE USER [DbUserAcct] FOR LOGIN [DbLoginAcct]
GO
ALTER USER [DbUserAcct] WITH DEFAULT_SCHEMA=[dbo]
GO
-- We want this user to have full control of the database so make it a db_owner
ALTER ROLE [db_owner] ADD MEMBER [DbUserAcct]
GO

After running the script we can log into the database by using the DbLoginAcct credentials.  If this is the first account on the server using database authentication you may run into issues when authenticating.  To fix these issues take a look at  How to Fix Microsoft SQL Server Login Failed Error 18456?

Finally, two things worth noting are the difference between the LOGIN and USER objects created as part of the script.  As it relates to the server, the LOGIN is used for authentication and the USER is used for authorization.  In other words, the LOGIN gets you into the server while the USER gets you access to the specific database.

Update:  This is for a non-Azure SQL Server Instance.