SQL Server script to create a database, login, user and permission

The simplest way to bring up a new database and user for testing and development.

Luke Lowrey
Luke Lowrey
- 1 min read
-- Create a new database called 'test-database' with user 'test-database-user' with password 'test-database-password-!@#$@!1'
if db_id('test-database') is null
BEGIN EXEC ('create database [test-database]') END
IF NOT EXISTS (SELECT name FROM master.sys.server_principals WHERE name = 'test-database-user')
BEGIN EXEC sp_addlogin 'test-database-user', 'test-database-password-!@#$@!1', 'master'; END
use [test-database] IF NOT EXISTS (SELECT * FROM [sys].[database_principals] WHERE [type] = 'S' AND name = N'test-database-user') BEGIN CREATE USER [test-database-user] FOR LOGIN [test-database-user] WITH DEFAULT_SCHEMA=[dbo] END
EXEC ('use [test-database] EXEC sp_addrolemember ''db_owner'', ''test-database-user''')

During development I am often firing up new database, especially for testing migrations and seed scripts. This script is the quickest way I found to bring up a new database and user.

It works well with Azure Data Studio as a user snippet (grab it from my Gist here).

Azure Datastudio Create DB