Create an additional SQL Server Login

Sometimes you need to create an additional SQL Server Login (user) for your database (databases). Here is a small T-SQL snippet that creates such a login for contained databases in SQL Server and adds memberships.

Use contained database users to authenticate SQL Server and SQL Database connections at the database level. A contained database is a database that is isolated from other databases and from the instance of SQL Server/ SQL Database (and the master database) that hosts the database. SQL Server supports contained database users for both Windows and SQL Server authentication.

SQL Docs – Contained Database Users – Making Your Database Portable

In the following example I have an SQL Server database “exampleorg”, an user “exampleorg” and a password known to me (“known_password”). Use this T-SQL to create an addition SQL Server user (login) and memberships. Substitute example values with your values:

USE [exampleorg]
CREATE USER [exampleorg] WITH PASSWORD=N'known_password', DEFAULT_SCHEMA=[dbo]
ALTER ROLE [db_datareader] ADD MEMBER [exampleorg]
ALTER ROLE [db_datawriter] ADD MEMBER [exampleorg]
ALTER ROLE [db_ddladmin] ADD MEMBER [exampleorg]
ALTER ROLE [db_securityadmin] ADD MEMBER [exampleorg]
GO

GRANT EXECUTE ON SCHEMA::dbo TO [exampleorg]
GOCode language: SQL (Structured Query Language) (sql)

What this does, in a nutshell:

1. a new user is created in the database exampleorg, called exampleorg;
2. the new users default schema is the dbo schema;
3. the user is added as a member to the following fixed-database roles:
   • db_datareader: Members of the db_datareader fixed database role can read all data from all user tables and views. User objects can exist in any schema except sys and INFORMATION_SCHEMA.
   • db_datawriter: Members of the db_datawriter fixed database role can add, delete, or change data in all user tables. In most use cases this role will be combined with db_datareader membership to allow reading the data that is to be modified.
   • db_ddladmin: Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database. Members of this role can potentially elevate their privileges by manipulating code that may get executed under high privileges and their actions should be monitored.
   • db_securityadmin: Members of the db_securityadmin fixed database role can modify role membership for custom roles only and manage permissions. Members of this role can potentially elevate their privileges and their actions should be monitored.
4. The newly created user exampleorg gets execute permission on the dbo schema;

Instead of ALTER ROLE statements you can also use sp_addrolemember stored procedures:

exec sp_addrolemember @membername = [exampleorg], @rolename = [db_datareader]
exec sp_addrolemember @membername = [exampleorg], @rolename = [db_datawriter]
exec sp_addrolemember @membername = [exampleorg], @rolename = [db_ddladmin]
exec sp_addrolemember @membername = [exampleorg], @rolename = [db_securityadmin]

Thank you very much! <3 ❤️