SQL Server: Users Without Logins?

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.

Query a range of possibilities for your future with SQL Server Training. Available In-Person, Online, or as Private Team Training!

This piece was originally posted on Feb 26, 2021, and has been refreshed with updated styling.

Written by Dan Buskirk

"The pleasures of the table belong to all ages." Actually, Brillat-Savaron was talking about the dinner table, but the quote applies equally well to Dan’s other big interest, tables of data. Dan has worked with Microsoft Excel since the Dark Ages and has utilized SQL Server since Windows NT first became available to developers as a beta (it was 32 bits! wow!). Since then, Dan has helped corporations and government agencies gather, store, and analyze data and has also taught and mentored their teams using the Microsoft Business Intelligence Stack to impose order on chaos. Dan has taught Learning Tree in Learning Tree’s SQL Server & Microsoft Office curriculums for over 14 years. In addition to his professional data and analysis work, Dan is a proponent of functional programming techniques in general, especially Microsoft’s new .NET functional language F#. Dan enjoys speaking at .NET and F# user’s groups on these topics.