How to Use Login-Less Users the Alternate to Application Roles?

SQL Server 2005 introduces a new type of database user, one that is created using the “WITHOUT LOGIN” clause. This user is not mapped to a login, and is being touted as an alternate to Application Roles because no password is needed.. What I do not understand is how to use this type of user. I know how to use sp_setapprole with Application Roles, what are the equivalent steps needed to use login-less users?

Thank you in advance.

Terry Duffy

As I recall, the [WITHOUT LOGIN] option allows a user into the server without a specific database login. That user is mapped to the GUEST account.

In my opinion, you never want to use the GUEST account, so I couldn't recommend exploring this any further. (But someone must have a good case for this option.)

|||

You can impersonate loginless principals (using EXECUTE AS USER/EXECUTE AS on a module) the same way you can impersonate regular SQL users.

The only difference is that, based on the SQL USER SID, the system can recognize that these principals don’t have any login mapped to them. It will create a low privileged login token for impersonation, and a full user token for the database.

If you want to use them in a similar fashion as application roles, you can use the WITH NO REVERT or WITH COOKIE clauses in EXECUTE AS (ad hoc). http://msdn2.microsoft.com/en-us/library/ms181362.aspx

You can use them as an application context (using EXECUTE AS in your SP/UDF definition) and grant permissions only to this loginless user. This way you will only need to grant permission to execute your application SP/UDF to other users instead of granting direct permissions on the resources.

I also talk about these new principals on my blog: http://blogs.msdn.com/raulga/archive/2006/07/03/655587.aspx

Let us know if you have any further questions or feedback.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

|||

Arnie, users without login are only mapped to guest if they switch context to another database than the one they're defined in - because guest is by default disabled in user databases, this means that users without login are restricted from going to other user databases. You don't have to use the guest account, to use a user without login - such user is useful by itself. This mapping to guest is actually the only significant difference between users without login and approles.

Thanks

Laurentiu

|||

Thank you all. Just yesterday I attended a MS security web cast where login less users were highly recommended, so I do intend to research this further. Where application roles require the use of a password, this new type of user does not. Raul, your blog was cited as a resource and I was familiar with your content.

If I am reading this correctly, the only way to use this type of user is with "EXECUTE AS", and there is no comparable functionality to sp_setapprole. Is this correct? If true, I just don't see any advantage to this type of user over using "EXECUTE AS" with a user with a login, and APPLICATION ROLES (despite the password) seem more secure.

Thanks again to all.

Terry

|||

Both EXECUTE AS (stand alone) and sp_setapprole impersonation mechanisms can be used in similar ways. Both have options to prevent reverting back to the original context and both have an optional cookie-based mechanism to revert to the original context.The main difference between approles & user without login is the mechanism to verify the right to impersonate them: Approles are based on passwords and loginless users are based on permissions.

While using password as a protection mechanism can be effective, from the management point of view, requiring a password for implies that the security relies on keeping the password safe and having good password policies around them.The most common pitfall when using approles is hardcoding the password in scripts and applications. I have seen this mal-practice multiple times, and besides the obvious problem that it only takes to find the approle once to compromise the security, there is no easy way to comply with password policies and change the password regularly.If an attacker knows the password for an approle the only way to keep her from abusing the approle is to change the password to something different (affecting also regular applications and users who will need to get the new password). For loginless users, you only need to revoke/deny permission to impersonate such user o the attacker.

If you decide to use approles (a valid option, as Microsoft fully supports the feature in SQL Server 2005) I strongly recommend having a strategy to avoid hardcoding the password in any script/application and have password policies (especially password maximum life-time) in mind.

We appreciate any comments and suggestions on how we can improve our security features. If you have any concerns or feedback regarding loginless users or approles, please let us know.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine