SQL Server - Scripting CREATE USER WITHOUT LOGIN

Question

I have a SQL Server instance using SQL Authentication only. I will have only two users and one database on this instance. The SA has a user name of XX. I have another user and lets say that user is X. And, in my create scripts, I am adding X as a user on the server and then on the one database that is there.

If XX is the SA (created when installing SQL Server) there is no need for me to explicitly map XX to any database, correct?

I am a little confused over the CREATE USER WITHOUT LOGIN. If the above is true would I ever need to script the addition of X to the one database WITHOUT LOGIN? What is the significance of WITHOUT LOGIN? Under what conditions would anyone what to do that?

Thank you.

JoseTeixeira · Accepted Answer · 2013-06-14 22:47:47Z

4

Users without login were added to replace application roles.
Loginless users are usefull for impersonation, in order to gain necessary permissions. They allow users to authenticate to the instance with their own credentials, therefore making SQL Server able to audit activity to their login, while impersonating the loginless user on the database context.

Simple impersonation example:

SELECT SUSER_NAME(), USER_NAME();
GO
CREATE USER loginless_user_4test
    WITHOUT LOGIN
GO
EXECUTE AS USER = 'loginless_user_4test'
GO
SELECT SUSER_NAME(), USER_NAME();
GO
REVERT  --as long as you haven't issued "EXECUTE AS ... WITH NO REVERT", you can go back to previous context
GO

edited Jun 14, 2013 at 22:47

answered Jun 14, 2013 at 17:14

JoseTeixeira

1,2968 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Mike Malter Over a year ago

Your answer is very helpful, thank you. Would it be possible for you to provide a small example of impersonation? I use database roles as a part of my security approach and I have never used an application role.

Collectives™ on Stack Overflow

SQL Server - Scripting CREATE USER WITHOUT LOGIN

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related