Quick guide to DB users without logins in SQL Server 2005


  SQL Server 2005 introduced a new SQL DB principal subtype that can be quite useful: a SQL user that is not mapped to any login. You may be asking yourself “Why is this feature interesting? after all SQL Server already had the ability to create SQL users”, well, to answer this question I would like to describe what this SQL principal subtype really is and what interesting properties it has.


 


  For most operations, these subtype of users behave the same way as regular SQL users. They can own objects and schemas, can be granted/denied permissions, can be impersonated, etc.; The difference as the DDL describes is that these are DB-scoped principals not mapped to any login.


 


  Because there is no mapping on these principals there is no need to prerequisite to generate a login, therefore a DBO can generate such principals at will, even without having access to create/guess logins.


  CREATE DATABASE db_Demo


go


— Create a login whose only purpose is to manage the db_Demo DB


— no other permissions at server or crossDB scope are granted


CREATE LOGIN db_Demo_dbo WITH PASSWORD = ‘My dem0 p@ssw0Rd’


go


 


ALTER AUTHORIZATION ON DATABASE::db_Demo TO db_Demo_dbo


go


 


USE db_Demo


go


 


— Switch to db_Demo_dbo


EXECUTE AS LOGIN = ‘db_Demo_dbo’


go


 


— Let’s create a user for my application, but do we have a


— login named my_app_login?


CREATE USER my_app_login


go


 


— As you can see, the access to sys.server_principals


— is limited. Forcing this DBO to “guess” the name of


— a login in order to create a user …


SELECT name FROM sys.server_principals


go


— … and of course, creating a new arbitrary login is out of question


CREATE LOGIN my_app_login WITH PASSWORD = ‘My dem0 p@ssw0Rd’


go


 


— USER WITHOUT LOGIN on the other hand does not require


— any additional permission


CREATE USER my_app_user WITHOUT LOGIN


go


 


— Succeeded!, Let’s take a quick look to the MD


— As youcan see, except for the SID, this looks like an ordinary SQL user


SELECT * FROM sys.database_principals WHERE name = ‘my_app_user’


go


 


REVERT


go


 


  In many situations, especially for ISVs, it may be interesting to create a DB user to own a schema and objects used in any given application, or to mark modules with execute as and granting only permissions to these principals. For this scenario, creating a regular SQL user will require to create a login with a password, this may affect the ability to script the application as well as potentially polluting the server principals information; using a user without login for this purpose may be a very good alternative that requires no password (making it easier to script).


 


— Create a schema for the application objects


— making my_app_user the owner of all of them


CREATE SCHEMA my_app_schema AUTHORIZATION my_app_user


go


 


— my_app_user is the owner (via schema ownership) of this table


CREATE TABLE my_app_schema.table1( data int )


go


 


— create a demo table that is required by the app,


— but my_app_user is not the owner


CREATE TABLE dbo.table2( data int )


go


 


— GRANT access to the demo table to my_app_user


GRANT SELECT ON dbo.table2 TO my_app_user


go


 


— Create a module that will always run as my_app_user


CREATE PROC my_app_schema.sp_demo


WITH EXECUTE AS ‘my_app_user’


AS


  SELECT user_name()


  SELECT * FROM my_app_schema.table1


  SELECT * FROM dbo.table2


go


 


  One of my favorite ways to use users without login is to test if my application works with minimum permissions. As there is no need to create logins, I don’t expose or pollute my server in any way, and it is usually easy to clean up these users on a dev environment.


 


— Let’s test the app


— I will create a user w/out login to test


CREATE USER my_app_tester WITHOUT LOGIN


go


 


— This is the minumum permission needed to run my app


GRANT EXECUTE ON my_app_schema.sp_demo TO my_app_tester


go


 


EXECUTE AS USER = ‘my_app_tester’


go


 


— Should succeed and run as my_app_user


EXEC my_app_schema.sp_demo


go


 


— direct access should fail


SELECT * FROM my_app_schema.table1


SELECT * FROM dbo.table2


go


 


REVERT — my_app_tester


go


 


Now here is a quite interesting question: If there is no login for this user, how is it possible to impersonate the user and what is the behavior outside the current database?


  Impersonation is possible as these type of users are mapped to special type of SID. This SID belong to a special family that indicates that the information used to create the login token is not available in metadata, instead, the login information must be generated on the fly (pretty much a “public access only” generic token). Let’s take a look in detail to the tokens:


 


EXECUTE AS USER = ‘my_app_tester’


go


 


— Let’s look at the token


SELECT principal_id, sid, name, type FROM sys.login_token


SELECT principal_id, sid, name, type FROM sys.user_token


go


 


REVERT — my_app_tester


Go


 


Login token:



















principal_id


sid


name


type


0


0x010500000000000903000000…


S-1-9-3-…


Sql login


2


0x02


public


Server role


 


Notice that the principal_id is 0. This is a special id and refers to a principal that is not in metadata, also take a look to the SID and name, the name is really a string representation of the SID.


 


User token:



















principal_id


Sid


name


type


<#>


0x010500000000000903000000…


My_app_tester


SQL USER


0


Null


public


ROLE


 


The user token on the other hand looks exactly the same as anty other database principal token would look like.


 


Now the next question: Can I access other databases or server resources while impersonating these subtype of users?


  Unlike approles, that are truly DB scoped (the login token for approles is a special case, and it is never trusted on the server) the user without login tokens are bound to the same trust relationship as any other user impersonation. You can use digital signatures (recommended) or trustworthy bit (personally, I don’t recommend using this option) to establish a trust relationship to access server resources.


 


— Modify the app to access a server resource


— in this case we will use VIEW ANY DEFINITION


ALTER PROC my_app_schema.sp_demo


WITH EXECUTE AS ‘my_app_user’


AS


  SELECT user_name()


  SELECT count(*) FROM sys.server_principals


  SELECT principal_id, sid, name, type, usage FROM sys.login_token


  SELECT principal_id, sid, name, type, usage FROM sys.user_token


go


 


— Let’s run the app as our test user


EXECUTE (‘EXEC my_app_schema.sp_demo’ ) AS USER = ‘my_app_tester’


go


 


— No surprises so far, now let’s grant VIEW ANY DEFINITION to public


— we have to revert to sysadmin for this one!


REVERT — db_Demo_dbo


go


 


use master


go


 


GRANT VIEW ANY DEFINITION TO public


go


 


— Let’s see how many logins we have, in my case it’s 35


SELECT count(*) FROM sys.server_principals


go


 


— Let’s go back to the demo DB and test the app again


— Let’s run the app as our test user


USE db_Demo


go


EXECUTE (‘EXEC my_app_schema.sp_demo’ ) AS USER = ‘my_app_tester’


go


 


— What happened?! This time we only got 10 logins back


— As you can see in teh login token info, the login token is not trusted (deny only)!


  Let’s sign the module and establish the proper trust relationship


CREATE CERTIFICATE my_app_cert ENCRYPTION BY PASSWORD = ‘My c3r+ p@zzw0Rd’ WITH SUBJECT = ‘My app signing cert’


go


ADD SIGNATURE TO my_app_schema.sp_demo BY CERTIFICATE my_app_cert WITH PASSWORD = ‘My c3r+ p@zzw0Rd’


go


— Backup the cert and it’s PVK and remove it from teh DB


BACKUP CERTIFICATE my_app_cert TO FILE = ‘my_app_cert.cer’


  WITH PRIVATE KEY( FILE = ‘my_app_cert.pvk’, ENCRYPTION BY PASSWORD = ‘My c3r+ p@zzw0Rd’, DECRYPTION BY PASSWORD = ‘My c3r+ p@zzw0Rd’  )


go


ALTER CERTIFICATE my_app_cert REMOVE PRIVATE KEY


go


 


— Now go to master and create the cert, a login map to it and grant the appropiate permission


USE master


go


 


CREATE CERTIFICATE my_app_cert FROM FILE = ‘my_app_cert.cer’


go


 


CREATE LOGIN my_app_cert FROM CERTIFICATE my_app_cert


go


 


GRANT AUTHENTICATE SERVER TO my_app_cert


go


 


— Now that the cert is vouching for the context, let’s try again


USE db_Demo


go


EXECUTE (‘EXEC my_app_schema.sp_demo’ ) AS USER = ‘my_app_tester’


go


— Success!!!


— Notice that in the login token the certificate will work as both a


— secondary identity and as authenticator


— For this demo, we are only using it as authenticator.


 


  These are just a few examples on how this new type of principal can be used along with other features in SQL Server 2005 based on the ways I typically use them, but I am sure you will find other new interesting way to take advantage of this feature.


 


 I hope this article has been helpful.

User_without_login.sql

Comments (9)

  1. raulga says:

     Another good use of users without logins I forgot to mention is as a replacement for application roles (approles) when combined with the optional “WITH NO REVERT” or “WITH COOKIE” clauses.

     The main advantage of using users without logins rather than approles is that permission to impersonate is based on permissions and not on a password, eliminating the need to share a password that most likely will be hardcoded into an application.

    CREATE USER [myAppUser] WITHOUT LOGIN

    go

    — …

    — Your application can impersonate the user without login instead of calling sp_setapprole

    EXECUTE AS USER = ‘myAppUser’ WITH NO REVERT

    go

    SELECT user_name()

    go

    — … after that it won’t be possible to revert to the original context

    REVERT

    go

  2. Nates Stuff says:

    These past two weeks have been very exciting for me. I have gotten to be involved in some R&amp;D for

  3. Subrahmaniam says:

    How to differentiate the db user without login and orphaned user in the system catalog view?

  4. raulga says:

     Pretty much the only way to distinguish between a user and a user without login is by looking at the SID. Regular SQL users will have a GUID as a sid (16 bytes) while users without login will have a SID structure (28 bytes for current version of SQL Server). The queries below may help.

    — Finding users without login

    SELECT * FROM sys.database_principals

    WHERE

    DATALENGTH(sid) > 16 — users mapped to logins have a 16 bytes SID,

     — users without login have a length of ~28 bytes

    AND sid not in (SELECT sid FROM sys.server_principals) — No login with a matching SID

    AND type = 'S' — Only SQL users

    AND principal_id > 4 — filter system (well-known) principals

    go

    — Finding orphaned users

    SELECT * FROM sys.database_principals

    WHERE

    DATALENGTH(sid) <= 16 — users mapped to logins have a 16 bytes SID,

     — users without login have a length of ~28 bytes

    AND sid not in (SELECT sid FROM sys.server_principals) — No login with a matching SID

    AND type = 'S' — Only SQL users

    AND principal_id > 4 — filter system (well-known) principals

    go

  5. matt says:

    One question, why I comments 'CREATE USER [mytestDB] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]', when I deploym db to the remote site, the db connection will generate error? If I used it in local site, it has no error?

    Could you pls give me some comments? Thanks a lot.

  6. I encountered a issue when I comment the command 'CREATE USER [mytestDB] WITHOUT LOGIN WITH DEFAULT_SCHEMA=[dbo]', if I deployed the db to the remote site, the DB connection has error, but if I used it in local site, it's no DB connection error.

    Could you pls give me some idea?

    Thank you very much.

    Matt.

  7. raulga says:

     Can you please provide more information about the error? I cannot repro on my own, but I may be able to recognize the root cause from the error description. There shouldn’t be any different behavior whether the statement you mentioned is executed remotely or on the local server.

        If the error persists, I would also recommend opening a bug @ Microsoft connect (connect.microsoft.com/sql).

  8. Mark Burns says:

    Raul,

    I find this topic very interesting, and I would like to get some advice for using these NLI-Users (No LogIn Users) as that Application Role replacement from the POV of an MS Access FE + SQL Server BE Application Developer.

    In general it'd be best for an application (regardless of AD user logins and groups) to have either little or no actual table/view access from the linked tables (enough access permissions to create the linked tableDef in the Access DBs, but not to pull data from the server) from the old Database Window (or the newer navigation menus). Ideally, then we could launch access permissions based upon these users w/o logins on a form-by-form/report-by-report/code-module-by-code-module basis, greatly enhancing the security of the application and data.

    Could you offer any insights as to how to best approach this idea – taking into account the differing needs of the App developer (in a DEV  or TEST server environment of course) an actual SQL Server DBA's roles and access requirements, and the (TEST + PROD servers) needs of varying groups of users (think AD Groups) for multiple permutations of read and write access to the different data tables, depending upon database functions employed and their AD group memberships.

  9. raulga says:

    Quick disclaimer in case you haven’t seen it yet. I am no longer part of the SQL Server group, but I will keep posting and responding to questions/comments on SQL Server topics on this blog.

    Users without login are similar to approles, with the main difference that application roles are set and controlled via a password (i.e. middle tier needs to know the password), which brings by itself all sort of password management issues, while users without login are permission based (IMPERSONATE permission on the user).

    This is a significant difference when dealing with code. When people use approles, I have often seen hardcoded passwords as a simplification for the developers dealing with them, but this typically causes a lot of problems for DBAs/ops who need to conform to password policies. The user without login approach on the other hand can simplify the password administration.

    Even more, considering test & prod environments, the deployment would be pretty much the same, without risk of the devs having access to the prod environment (unless DBA explicitly grants them access to the database and permission to impersonate the application-specific user). Because impersonation is permission based, the impersonation action itself can be audited.

    Another great advantage of user without logins, is that you can use them with EXECUTE AS within a module (i.e. SP) and grant permissions via the SP directly instead of granting permissions on the underlying tables to the SP caller. Once the SP execution finishes, the execution context would revert back to the caller automatically.

    Because impersonation is permission based, you can also take advantage of AD security groups, and grant IMPERSONATE permission on the application user (or EXECUTE permission on the SP if you prefer the EXECUTE AS approach) and take advantage of managing security via Windows security groups.

    Both the users with no-logins and the application roles are very similar in terms of access to server-scoped resources. As I recall, the main difference is that approles have some limited access to some catalog views (metadata) for backwards compatibility purposes. By default access to other databases is also similar (only via “guest”).

    The main difference regarding permission management for users without login is that there are supported mechanisms to have a controlled escalation of privileges to full server-scope of across databases (i.e. trustworthy bit on the DB or usage of digital signatures).

    I hope this helps,

    -Raul