Execute As does not require SQL logins

I ran into an interesting question this week, and had to do some digging around to find the answer. The issue was from a vendor, and boiled down to "we need to have SQL Authentication enabled on your server (mixed mode) because even though we are not connecting to the SQL Server with SQL auth, we are using Execute As to change the security context for stored procedures, using multiple schemas, and that requires SQL accounts." I told them I didn't think those two things were related, but I had to do a little research to be sure. As far as I can tell, they are mostly unrelated.

CREATE PROCEDURE ... WITH EXECUTE AS typically points to a specific user in a database. If you've ever tackled the common "orphaned user" issue, for example when moving data between servers, then you probably know that a user and a login are separate objects. A user object is in the context of a specific database, and a login is a server-wide object. Database user objects are typically linked to server logins, but that isn't a requirement. In fact, the Create User statement has an optional clause "WITHOUT LOGIN" that will make a user in a database and not link it to any server login.

So, if your security design involves encapsulating logic in stored procs that execute as a user with higher privileges (good idea!) and for some reason you want to use an explicit user instead of dbo, then you can, if you want, create the elevated-priv user as any of these options:

• Linked to a SQL Login (unless you try to avoid SQL Authentication, as I do)
• Linked to a Windows Login
• Not linked to any Login (this has the interesting property that no one can log in to the server using this identity)

The only limitations, as far as I know, are that if the user isn't connected to any login at all, then it will only have permissions within your single DB, and can't manipulate data in other databases on the same server except as "guest." The user also cannot execute any server-wide operation. That makes sense; manipulating things across multiple databases would demand the permissions in the target database provided by a login. BOL has the details under "Execute As Clause."

Need proof? Try this out:

CREATE DATABASE ExecAsTestDB 
GO 

USE ExecAsTestDB 
GO 

-- Create samples including multiple schemas 
CREATE SCHEMA report_schema 
GO 
CREATE SCHEMA foo_schema 
GO 

-- Make one login-less user and one user linked to a Windows login 
CREATE USER report_db_user WITHOUT LOGIN WITH DEFAULT_SCHEMA = report_schema 
CREATE USER foo_db_user FOR LOGIN [mydomain\foo] WITH DEFAULT_SCHEMA = foo_schema 
GO 

-- Test tables 
CREATE TABLE report_schema.report_data ( val VARCHAR(100) ) 
CREATE TABLE foo_schema.foo_data ( val VARCHAR(100) ) 
GO 

-- Grant permissions at the schema level to the test users 
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::report_schema TO report_db_user 
GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA::foo_schema TO foo_db_user 
GO 


-- Procedures that run in the context of each user, and as dbo 
CREATE PROCEDURE dbo.do_report_things  
WITH EXECUTE AS 'report_db_user' 
AS 

BEGIN 
   INSERT INTO report_schema.report_data (val) VALUES ('Grok') 
END 
GO 

CREATE PROCEDURE dbo.do_foo_things  
WITH EXECUTE AS 'foo_db_user' 
AS 

BEGIN 
   INSERT INTO foo_schema.foo_data (val) VALUES ('Bar') 
END 
GO 

CREATE PROCEDURE dbo.do_foo_things_as_dbo  
WITH EXECUTE AS 'dbo' 
AS 

BEGIN 
   INSERT INTO foo_schema.foo_data (val) VALUES ('Bar') 
END 
GO 

-- Test the procs 
EXEC dbo.do_report_things 
EXEC dbo.do_foo_things 
EXEC dbo.do_foo_things_as_dbo 

SELECT * FROM report_schema.report_data 
SELECT * FROM foo_schema.foo_data 
GO 

Obviously, test your stuff carefully if you try this trick.