SQL Server: Separating Developer and Tester Database

Create a database schema named Special. Create a Contractor database role and grant the role SELECT and VIEW DEFINITION permissions on the dbo schema and the CONTROL permission on the Special schema. Grant the desired CREATE permissions to the Contractor role.Create a user account for each contractor with the Special schema as the default schema. Make each contractor a member of the Contractor role. Schemas contain database objects. Using schemas allows DBA/Developer to manage ownership and permissions of database objects more effectively. In this scenario, DBA/Developer should create a separate schema to contain all database objects that contractors create. This will allow DBA/Developer to keep the contractors' database objects logically separated from the objects created by the development team. The schema name is displayed in Object Manager to make objects easier to identify. DBA/Developer can also grant permissions at the schema level to simplify the management of permissions. If DBA/Developer grant a permission at the schema level, the same permission is implicitly granted for all database objects within the schema, even future objects. DBA/Developer can use the CREATE SCHEMA statement to create a schema, and optionally specify an AUTHORIZATION clause to specify the schema's owner. The schema's owner may be a user or role. If no schema owner is specified, dbo is the default schema owner. In this scenario, DBA/Developer could create the Special schema owned by dbo using the following statement: CREATE SCHEMA Special; Next, DBA/Developer need to give contractors the ability to perform their necessary tasks. The best way to implement this is to create a database role and grant the role the needed permissions. Then, DBA/Developer can make each contractor a member of the role. Permissions may be granted at the schema level instead of at the object level. Therefore, DBA/Developer could use the following Transact- SQL to create the Contractor role and grant the role the necessary permissions:

-- Create the Contractor role CREATE ROLE Contractor;

GO

-- Allows contractors to query and view the definitions of table in -- the dbo schema GRANT

SELECT, VIEW DEFINITIONON SCHEMA::[dbo] to [Contractor];

GO

-- Allows contractors to control Special schema

GRANT CONTROL ON

SCHEMA::[Special] to [Contractor];

GO

-- Allows contractors to create tables, views, and stored procedures-- in the database GRANT

 
CREATE TABLE, CREATE VIEW,
CREATE PROCEDURE TO [Contractor];
GO

After DBA/Developer create the Contractor role, DBA/Developer should then create contractor accounts with the Special schema as a default schema and make them a member of the Contractor role. When a member of the role creates a database object, it will be created in the user's default schema. The following Transact-SQL will create the Contractor1 user with a default schema of Special and assign the user to the Contractor role:

CREATE LOGIN [Contractor1]

WITH PASSWORD=N'12345'
GO

CREATE USER [Contractor1]

FOR LOGIN [Contractor1]
WITH
DEFAULT_SCHEMA=[Special]
GO EXEC sp_addrolemember N'Contractor ', N'Contractor1'

--Allow user to connect to database

GRANT CONNECT TO [Contractor1]

DBA/Developer can specify a schema as the user's default schema before the schema has even been created. DBA/Developer should note that if the user is authenticated via a Windows group, the user will have no default schema assigned. If such a user creates an object, SQL Server will create a new schema in which the object is created. The new schema created will have the same name as the user that created the object. When a schema is no longer needed, such as when the project is complete, DBA/Developer can drop it using the DROP SCHEMA statement. To be dropped, a schema must be empty, or the DROP SCHEMA statement will generate an error. Therefore, in this scenario, DBA/Developer could first move the objects from the Special to the dbo schema. DBA/Developer can use the TRANSFER clause of the ALTER SCHEMA statement to transfer objects from one schema to another. After transferring all objects to another schema, DBA/Developer could drop the schema. The following Transact-SQL will move the ContractorTbl table from the Special schema to the dbo schema and then drop the Special schema:

ALTER SCHEMA dbo
TRANSFER Special.ContractorTbl;
DROP SCHEMA Special;