Some questions are very open ended and it is very hard to come up with exact requirements. Here is one question I was asked in recent User Group Meeting about user without login.
Question: “In recent version of SQL Server we can create a user without login. What is the use of it?”
Great question indeed. Let me first attempt to answer this question, but after reading my answer I need your help. I want you to help him as well with adding more value to it.
Answer:
Let us visualize a scenario. An application has lots of different operations and many of them are very sensitive operations. The common practice was to do give application specific role which has more permissions and access level. When a regular user login (not system admin), he/she might have very restrictive permissions. The application itself had a user name and password which means applications can directly login into the database and perform the operation. Developers were well aware of the username and password as it was embedded in the application. When developer leaves the organization or when the password was changed, the part of the application had to be changed where the same username and passwords were used. Additionally, developers were able to use the same username and password and login directly to the same application.
In earlier version of SQL Server there were application roles. The same is later on replaced by “User without Login”. Now let us recreate the above scenario using this new “User without Login”. In this case, User will have to login using their own credentials into SQL Server. This means that the user who is logged in will have his/her own username and password. Once the login is done in SQL Server, the user will be able to use the application. Now the database should have another User without Login which has all the necessary permissions and rights to execute various operations. Now, Application will be able to execute the script by impersonating “user without login – with more permissions”.
Here there is assumed that user login does not have enough permissions and another user (without login) there are more rights. If a user knows how the application is using the database and their various operations, he can switch the context to user without login making him enable for doing further modification. Make sure to explicitly DENY view definition permission on the database. This will make things further difficult for user as he will have to know exact details to get additional permissions.
If a user is System Admin all the details which I just mentioned in above three paragraphs does not apply as admin always have access to everything. Additionally, the method describes above is just one of the architecture and if someone is attempting to damage the system, they will still be able to figure out a workaround. You will have to put further auditing and policy based management to prevent such incidents and accidents.
I guess this is my answer. I read it multiple times but I still feel that I am missing something. There should be more to this concept than what I have just described. I have merely described one scenario but there will be many more scenarios where this situation will be useful. Now is your turn to help – please leave a comment with the additional suggestion where exactly “User without Login” will be useful as well did I miss anything when I described above scenario.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
11 Comments. Leave new
Nice article, but I want to know how to create User Without Login and how to use it.
I have already answer how to use it. Creating is a single line script
Create user without login
Done!
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.
John,
This is excellent comment sir. I am totally with you on this subject. This subject I am learning and exploring. I am still not clear that how User Without Login will go mainstream. In industry people are not using it much thinking this is something does not help. There are few advantages it provides but again I just see that tendency of the user is to trust the application and many desktop application matter of fact provides a special screen for authentication and that just goes against what I wrote in this blog post.
I believe there is some gap in understanding of this feature in the industry and I totally agree with your comment with not having much to add to it on personal front.
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