Smart use of schemas

The schemas of HumanResources and Person share the same owner of dbo.

USE 1:
The User user2 does not have permissions on schema Person.
Because the owner of the stored procedure and schema Person are same, the user2 can execute the HumanResources.uspSSS stored procedure without any error.

Why?

When a user has the EXECUTE permission on a stored procedure, the Database Engine checks the owner of schemas: HumanResources and Person referenced. If they share the same owner, no permission check is required.


USE MASTER
GO
CREATE LOGIN login1 WITH PASSWORD='password'
GO
USE AdventureWorks
GO
CREATE USER user2 FOR LOGIN login1
GO
GRANT EXECUTE ON HumanResources.uspSSS TO user2
GO
EXECUTE AS USER='user2'

SELECT SUSER_NAME(),SUSER_SNAME()

EXEC HumanResources.uspSSS

Observation:
If you change the owner of schema Person to another user, e.g. testUser, when you re-run the HumanResources.uspSSS, error occurs. Why? The stored procedure has EXECUTE AS CALLER option, because the Owners of HumanResources and Person are different, Database Engine will check who want to access the database object. Here is User2. User2 does not have permissions on objects in Person schema.


Change the owner of Person from dbo to another user, for example, testUser,
Re-run the HumanResources.uspSELECT, an error reports.

USE 2:simplify administration

If you have many Web Applications (e.g. 1000) to access your databases by using stored procedures, you can use the schemas to simplify the process.

The following is an example:

• Create a schema owned by the ASPNET account;
• assign the schema as the owner of all database items created;
  
• All applications should be configured to impersonate the ASPNET account.
• Stored procedures and executable code in the database should be configured to execute as the ASPNET account.

If you create the stored procedures with EXECUTE AS CALLER option, there will be many login and database users to be created and maintained.

USE 3: isolate database objects

How should you design the database to allow only database developers access to database components?

1. Add all developers to the Developers group.
2. Create a schema Dev that owns all database objects.
3. Grant only the Developers group permissions to the Dev schema.

====

Another use of Schema

AdventureWorks database has a view: Sales.vSalesPerson.

Sales.vSalesPerson depends on tables: Address, Contact, Employee, EmployeeAddress, and SalesPerson.

You create a login X1 and database user X1 without any perssion granted on any table. Run the statement with user X1: Select * From Sales.vSalesPerson

The SELECT permission was denied on the object 'vSalesPerson', database 'AdventureWorks', schema 'Sales'.

Create a stored procedure:

use adventureworks
GO
CREATE PROCEDURE Sales.getContact
AS
BEGIN

SET NOCOUNT ON;
SELECT * from Sales.vSalesPerson
END
GO

GRANT EXECUTE ON Sales.getContact to X1

And then, connect to SQL Server as X1,

You can run the statement without any error.

USE AdventureWorks
EXEC Sales.getContact