CREATE DATABASE payrolldb
GO

USE payrolldb
GO

CREATE LOGIN janet WITH PASSWORD = 'temppwd',
DEFAULT_DATABASE = payrolldb
GO

-- default named schema
CREATE USER janet FOR LOGIN janet
WITH DEFAULT_SCHEMA = prschema
GO

CREATE ROLE payroll
GO

sp_addrolemember 'payroll', 'janet'
GO

CREATE SCHEMA prschema AUTHORIZATION payroll
GO

GRANT CREATE TABLE TO janet
GO


EXECUTE AS USER='janet'
GO

-- this statement would fail
CREATE TABLE benefits2003 (empid INT) -- other columns elided
GO

-- this statement would succeed
CREATE TABLE prschema.benefits2003 (empid INT)
GO

DENY VIEW DEFINITION ON schema::prschema TO public
GO