SQL Server 2005 Schema And Security

Hi everyone,

I'm currently investigating the security improvements of SQL Server 2005. I've got some problems with the schemas introduced in SQL 2005 and security settings.

For my test I've created two schemas: UserManagement and Sales. A user "test" is attached to the UserManagement schema. There's a table Sales.Users containing a list of users (varchar) and a stored procedure named UserManagement.AddUser that can be executed by the UserManagement schema (GRANT EXECUTE, so "test" can execute the SP). UserManagement.AddUser simply inserts a new row into Sales.Users.

Because the Sales schema doesn't contain any user, nobody (except the sysadmin, of course) can do a INSERT/SELECT/DELETE in the Sales.Users table. As expected, the following SQL statement fails:

EXECUTE AS LOGIN='machine est';
INSERT INTO Sales.Users VALUES('Test User');

INSERT was not allowed: object 'Users', database 'test', schema 'Sales'.
The second way of inserting rows into Sales.Users is to execute the stored proc UserManagement.AddUser:
CREATE PROCEDURE [UserManagement].[AddUser]
WITH EXECUTE AS CALLER
AS
INSERT INTO Sales.Users VALUES('Test User');
The user "test" can execute this sproc without problems:
EXECUTE AS LOGIN='machine est';
EXECUTE UserManagement.AddUser;

(1 row(s) affected)To my astonishment the INSERT statement inside the stored proc does execute - although UserManagement.AddUser and Sales.Users are two different schemas. Why is that, is there a chaining happening? To my understanding SQL Server should test INSERT rights on Sales.Users for the UserManagement schema and deny the INSERT statement because UserManagement isn't allowed to INSERT in the Sales schema.

Any ideas? Help regarding the issue is greatly appreciated.

Best regards,

Alex