2023-03-22
Anna P., a student in Learning Tree's course 8420, {course:8420}, asks, "What good is an SQL Server user with no login?". No good at all if you think of users just as people. Clearly, people need to log in before they can use SQL Server. However, users can also be thought of as entities that have the permissions to perform specific tasks on SQL Server. (Note how database folks love to use the word "entities". Sounds much more intellectual than "things".) This different way of thinking about users, as things with permissions, can be used in a very important way in the management of SQL security.
EXECUTE AS...
As you already know, the EXECUTE AS statement can be used to change the execution permission context for TSQL batch code, stored procedures, and functions (except in-line user-defined functions. EXECUTE AS can be used in other ways as well, including server-level code, but here we are considering the most common application, batch and stored procedure code executing at the database level.
There are four options. The first, EXECUTE AS CALLER, is the default and specifies that the stored procedure code executes at the same level of privilege as whoever invoked the stored procedure. SELF specifies execution at the level of the individual who created the stored procedure. OWNER may be applied if the developer creating the procedure needs the procedure to run as the owner, most likely "dbo". Our interest right now is the last option, EXECUTE AS 'username'.
It's important to note that 'username" cannot be a role or group or built-in account or anything like that. It must be the name of a database user. Using the name of an actual human database user would be a poor idea for several reasons. People get sick, they quit, they get fired. Sometimes they get promoted. We do not want to embed into our code a strong dependency on an unpredictable entity. Another reason not to use the name of an actual human user is the unlikelihood that a person has the exact set of permissions you want to grant the stored procedure. This is where the user without login comes in.
User Without Login
As its name implies, a user without login cannot log into SQL Server. However, the "user" exists as a database object and may therefore be granted or denied permissions as may any other user. These permissions, of course, will be exactly those required by your stored procedure code. No more, no less.
The same Management Studio dialog used to create "normal" users can be used to create a user without login.
You could type the T-SQL in the same time it would take to find the SSMS graphical interface
CREATE USER TheWhateverApp WITHOUT LOGIN;Once the user has been created, you can assign permissions to that user like any other. Users without logins can also be assigned to database roles, if that makes things easier.
Conclusions
Users without logins are very valuable when used in conjunction with the EXECUTE AS statement. The combination permits administrators to fine-tune the permissions granted to T-SQL code in batches, stored procedures, and many user-defined functions.
This piece was originally posted on Feb 26, 2021, and has been refreshed with updated styling.
