Quick guide to DB users without logins in SQL Server 2005

[raulgarciamsft]

First published on MSDN on Jul 03, 2006

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