Some questions are open-ended and difficult to address with exact requirements. Here’s one such question I was asked at a recent User Group Meeting: “In recent versions of SQL Server, we can create a user without login. What is the use of it?”
This is an excellent question! Let me try to answer it. However, I encourage you to add your insights to enhance the value of this explanation.
Answer
Let’s visualize a scenario. Consider an application that performs numerous operations, many of which are highly sensitive. Traditionally, applications were assigned specific roles with elevated permissions. When regular users logged in (non-system admins), they had limited permissions. However, the application itself required a username and password embedded in its code to connect to the database and perform its operations. This approach had several challenges:
- Developers could easily access the application’s credentials.
- If a developer left the organization or the password needed updating, parts of the application had to be modified.
- Developers could misuse the credentials to directly log in and perform unauthorized actions.
In earlier versions of SQL Server, application roles were used to address some of these issues. However, this was later replaced by the concept of creating a user without login.
How It Works
Now, let’s recreate the above scenario using this concept. Regular users log in to SQL Server with their own credentials (username and password). Once authenticated, they can access the application, which performs operations by impersonating the special user. This specific user is configured with additional permissions required for the application’s functionality but has no login of its own.
This setup ensures that:
- Regular users are limited to their own restricted permissions.
- Sensitive operations are executed only under the impersonation of the special user.
- Credentials of this special user are not accessible, reducing misuse risks.
To further enhance security, explicitly DENY VIEW DEFINITION permissions on the database. This makes it harder for unauthorized users to determine how the application interacts with the database or obtain additional permissions.
Caveats and Limitations
It’s important to note that this approach applies only to users with restricted permissions. If someone with System Admin privileges tries to exploit the system, these safeguards won’t be effective, as admins can access everything by design.
For further protection, implement additional auditing and policy-based management to monitor and prevent potential misuse. No single measure is foolproof against a determined attacker, but layered security makes exploitation more difficult.
A Call for More Scenarios
The example above illustrates just one use case. I’m certain there are many more scenarios where creating such a user is beneficial. I encourage you to share your ideas and experiences in the comments. Did I miss any important points? Are there other use cases for this feature?
Let’s collaborate and explore more about this topic together.
Reference:Â Pinal Dave (https://blog.sqlauthority.com), YouTube
11 Comments. Leave new
Nice article, but I want to know how to create User Without Login and how to use it.
Thanks for explaining this sir, it has certainly helped me to get a better idea of the potential usage scenarios for this security feature.
It is indeed an interesting area and one that I myself am still trying to come to terms with in understanding.
For example, BOL states that this scenario (a User without Login) is to be used with contained databases but then the whole point of a contained database is to remove the dependency on Instance level components(security in this case).
A Database User without Login, still needs a way to access the instance (currently provided by means of a Login and impersonation) that is controlled/provided at instance level and so in my mind the feature is not really satisfying the needs of a contained database. What do you think?
I can see how a “User Without Login With Password” satisfies the contained database concept but then we have similar security challenges as we had with Application Roles to contend with once again.
Apologies for the long comment :-) however I can see these security feature intricacies causing a lot of questions/confusion from Developers and I’m keen to understand things as best I can, so that I can advise them on the best course of action.
I stumbled across a few lines in the MCTS SQL Server 2008 book (70-432) that loginless users were planned as a alternative to application roles. It is a conceptional difference. With an app role you “only need” a approle name and a password to gain access, while impersonate a loginless user must be delegated by an admin. This is really a major point which mostly developers (sorry to say that) won’t really understand.
I think there is a difference between a loginless user inside a “normal” database and a user inside a contained database:
As far as Is understand BOL and combine it with my little knowledge: We can have just a loginless user inside a contained database as well, but then you’ll use it only for “internal purposes” e.g. different schema.
But you cannot authenticate to the contained db.
On the other hand you can have the contained database user with password which is used in the connection string.
“When connecting to a contained database, if the user does not have a login in the master database, the connection string must include the contained database name as the initial catalog. The initial catalog parameter is always required for a contained database user with password.”
The alternative is to have a user based on a windows login/group.
Maybe this information also helps a little bit:
https://blogs.msdn.microsoft.com/mvpawardprogram/2012/07/16/contained-databases-in-sql-server-2012/
But really a great wrap-up by Pinal. Thx.
This will help with automation routines quite a bit I think. I will personally use it as a vehicle to allow automated system processes such as maintenance audits to safely perform validated routines that may physically change data in a database as defined by an administrator or permission-ed user. I think it may be a vehicle of choice in databases that receive high amounts of data both from application based user input, and from data dumps. An import database may have certain features that can be triggered to process automatically whenever a file is imported. Users can be stopped from accessing a system through log-in, but for automation processes, you need an agent that can enact procedures and functions whenever triggered (literally by a database trigger, for by a scheduled job, or manual query), which means that something may come into contact with malicious code that can be read into the system through a data-file or other injection.
What I am not clear on is can you create several users with no log-in? Say for different levels of automated permission in an active-processing database?
In fact you CAN create multiples of that – I just tested it. I wish I could EDIT my post above instead of append to it.
You really make it seem so easy with your presentation but I find this topic to be actually something which I think I would never understand. It seems too complex and very broad for me. I’m looking forward for your next post, I will try to get the hang of it!
Hi Pinal Sir, Kindly help, I am a total newbie just learning programming website using IIS , Asp.Net , c# . I have deployed my site on a webserver which has a database server hosted on separate site. I have the admin user name and password for the database. The site does not create membership for login, but I have to access the database to store data of users (registration details). I have no knowledge of application role or user wo login. Is their any previous blog / book/ video where setting up of database for IIS asp.net application is explained step by step.
Thanks in advance
Thank you for wonderful article. I understand the two parts, login as a user into DB and execute it as a LoginLess user. Just wondering whether anyone has sample client side .net code for this?
Hi Can anyone please help me what is difference between Orphan and Loginless User and how we can recognize any user if it is orphan or loginless user