How To Create SQL User Without Login

We can create a user without login since SQL Server 2005 version. This feature was introduced instead of Application Role. You may want to read my article “What is Application Role in SQL Server”

Usually login and password information is written in the connection string and all users use the same username and password. When an application developer leave from work, the password and the connection string must be changed.

We create a user without login and grant necessary privileges to the user. Then, we grant impersonate permission on this user to application developers. Thus, application developers can act as if this user. Let’s explain the subject by making an example.

Let’s create a login named ApplicationDeveloperLogin on the Instance and only grant public permission to ApplicationDeveloperLogin in the TestDB database.

We will receive an error when we try to read a table in the Test database with this user we created.

Create a user with db_owner privilege on the database. (sql user without login)

Right click on “Users” and click “New User” on the Security tab of the TestDB database.

Select “SQL user without login” from “User type:” section.

Give a name to the user we will create from “User name:” section.

Select the default schema information from “Default schema:” section.

Go to the Membership section and grant db_owner privilege to this user on the database. If you wish, you can grant more specific privileges from the Owned Schemas and Securables sections.

We have created a user with db_owner privilege in the Test database without “login”.

Then, grant impersonate permission to the ApplicationDeveloperLogin named “SQL_User_Without_Login”.

After granting the necessary privileges, you will see that you will not receive an error when you perform the select operation again with ApplicationDeveloperLogin as follows.

If you did not execute the impersonete script above, you will receive an error as follows.

Msg 15517, Level 16, State 1, Line 3

Cannot execute as the database principal because the principal “[SQL_User_Without_Login]” does not exist, this type of principal cannot be impersonated, or you do not have permission.

Msg 229, Level 14, State 5, Line 5

The SELECT permission was denied on the object ‘MyTable’, database ‘TestDB’, schema ‘dbo’.

Another way to connect to a database without a login is to convert the database to Contained Database. You may want to read my article “What is Contained Database“.