SQL Server – Why and How to Use User Without Login

loginssql serverusers

I've been trying to find why, how and where to use Users Without Login and I didn't find my exact question or anything that gets closer.
Where I did my research (I also read other posts, saw Youtube Videos):

I was searching for ways to turn my db more secure and I recently came to know that there's an option to create user without login. I thought it is nice.
But I didn't understand how to use it (not create, use), then after my research I realized that this user accesses the db while impersonating another user/role.
I thought it would be great to not have a user and somehow access the db.
But when I saw that there's something called execute as I thought that it wasn't useful because why I would execute queries on this "login-less" user as it was another user?
Why I just don't use this user's permission?
Then I thought maybe this user has too much permissions, so I just create a new one with less permissions…

Okay, I don't know why you would create a user without login.
How should this be used?
It's stupid, but I tried to login and failed (sorry, but I'm used to login with users, I never thought on creating a user and not to login, I just know that users login to something, that's where my knowledge is).

So, I don't know why to create this type of user instead of a normal one?
I don't know how (like do I login, do I just type the query and to use it)?
I don't know where (like, do I need to make an C# app, do I use command…) to use?

My knowledge only takes me till here, I don't have a Senior DBA to ask.
Please explain in simple words if possible.

Best Answer

Imagine that you have somebody running an app, and this requires certain privileges. You do want to be able to log who is doing what and see who are logged in to your SQL Server. So each person need their own login (this can possible be one login created for a domain group).

You don't feel comfortable having these people connecting using SSMS and play around in the database. Since the app might need DELETE permissions on the Customers table (say), then the end-user now can do this from SSMS.

So you create a user without login and have the app use EXECUTE AS as this user. I.e., without EXECUTE AS, the end-user have no privileges at all in the database (i.e., the potential AD group you created a login for and its subsequent user in the database). But they do have privileges when they've done EXECUTE AS.

Is this read security? No, of course not. But perhaps better than either having no traceability or having the end-users being able to play around in the database "directly".

What about application roles? Yes, that can be tighter since they are password protected.

Another usage for this is when you are testing things, using EXECUTE AS USER. You want to play around with something permissions related and it is easier to assign the permissions to a user without login and then do your tests then having to create a login and a user for whatever you want to test.

Related Question