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.