SQL Server: How to permission schemas?

I fear that either your description or your conception of Ownership Chaining is unclear, so let me start with that:

“Ownership Chaining” simply refers to that fact that when executing a Stored Procedure (or View) on SQL Server, the currently executing batch temporarily acquires the rights/permissions of the sProc’s Owner (or the sProc’s schema’s Owner) while executing that SQL code. So in the case of a sProc, the User cannot use those privs to do anything that the sProc code does not implement for them. Note especially that it never acquires the Identity of the Owner, only it’s rights, temporarily (however, EXECUTE AS… does do this).

So the typical approach to leverage this for security is to:

1. Put all of the Data Tables (and all non-security Views as well) into their own Schema, let’s call it [data] (though typically [dbo] is used because it’s already there and too privileged for the User’s schema). Make sure that no existing Users, Schemas or Owners have access to this [data] schema.

2. Create a schema called [exec] for all of the sProcs (and/or possibly any security Views). Make sure that the owner of this schema has access to the [data] schema (this is easy if you make dbo the owner of this schema).

3. Create a new db-Role called “Users” and give it EXECUTE access to the [exec] schema. Now add all users to this role. Make sure that your users only have Connect rights and have no granted access to any other schema, including [dbo].

Now your users can access the data only by executing the sProcs in [exec]. They cannot access any other data or execute any other objects.

I am not sure if this answers your question (because I was uncertain what the question was exactly), so feel free to redirect me.

---

As for row-level security, here is how I always do it with the security scheme above:

1. I always implement row-level security as a series of Views that mirror-wrap every table and compare the User’s identity (usually with Suser_Sname() or one of the others) to a security list keyed from a security code in the row itself. These are the Security-Views.

2. Create a new schema called [rows], give it’s owner access to the [data] schema and nothing else. Put all of the Security-Views in this schema.

3. Revoke the [exec] owner’s access to the [data] schema and instead grant it data access to the [rows] schema.

Done. Now row-level security has been implemented by transparently slipping it between the sProcs and the tables.

---

Finally, here is a stored procure that I use to help me remember how much of this obscure security stuff works and interacts with itself (oops, corrected version of code):

CREATE proc [TestCnxOnly].[spShowProc_Security_NoEX]  as
--no "With Execute as Owner" for this version
--create User [UserNoLogin] without login
--Grant connect on database :: TestSecurity to Guest
--alter database TestSecurity set trustworthy on

--Show current user context:
select current_user as current_
, session_user as session
, user_name() as _name
, suser_name() as [suser (sproc)]
, suser_sname() as sname
, system_user as system_


--Execute As Login = 'UserNoLogin'
select current_user as current_
, session_user as session
, user_name() as _name
, suser_name() as [suser (after exec as)]
, suser_sname() as sname
, system_user as system_

EXEC('select current_user as current_
, session_user as session
, user_name() as _name
, suser_name() as [suser (in Exec(sql))]
, suser_sname() as sname
, system_user as system_')

EXEC sp_ExecuteSQL N'select current_user as current_
, session_user as session
, user_name() as _name
, suser_name() as [suser (in sp_Executesql)]
, suser_sname() as sname
, system_user as system_'

--Revert
select current_user as current_
, session_user as session
, user_name() as _name
, suser_name() as [suser (aftr revert)]
, suser_sname() as sname
, system_user as system_

[EDIT: corrected version of code)