Why and how and where to use user without login

Question

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):

• https://social.msdn.microsoft.com/Forums/sqlserver/en-US/9dbd233a-a105-473f-acab-6d8272d5772b/use-of-lt-sql-user-without-login-gt?forum=sqldatabaseengine

• https://blog.learningtree.com/sql-server-users-without-logins/

• https://blog.sqlauthority.com/2012/10/04/sql-server-importance-of-user-without-login-t-sql-demo-script/

• Map a Login to a User that was Created Without Mapping to a Login

• https://stackoverflow.com/questions/6468813/assign-a-login-to-a-user-created-without-login-sql-server

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.

Answer 1

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.

Answer 2

1. User without logins are needed when you sign stored procedure with a certificate

You create certificate inside user database, create user from certificate, grant permissions to this user, and sign stored procedure by certificate

This is needed for stored procedure to obtain required permissions, for example update or delete on particular table. Then you grant end-user permission to execute stored procedure. Result is that end-user can NOT update or delete from table directly, but using stored procedure, he can.

Stored procedure can have logic that allows to limit how many rows can be deleted/updated per 1 execution, or which exactly rows, do some checkups, etc. etc.

2. User without logins sometimes used in "Execute as" clause in a stored procedure or a script, so SP obtains permissions similar to what I explained above with certificate signing

3. User without logins can be temporarily created just to test some permissions by the DBA using "execute as user" clause (Tibor already explained in his answer)