Permissão para usuário em um schema específico

Hoje recebi uma solicitação diferente da equipe de desenvolvimento: liberar acesso total a um determinado esquema em um banco de dados da produção, para um desenvolvedor. Deixando de lado os motivos desta solicitação e a blasfêmia de liberar produção para um desenvolvedor, eu tive que apelar um pouquinho para o BOL, já que fazia muuuito tempo que eu não mexia com permissões.

Segue abaixo um passo a passo para liberar e testar esse tipo de permissão:

CREATE USER User1 WITHOUT LOGIN; GO

CREATE SCHEMA Schema2; GO

GRANT CONTROL ON SCHEMA::Schema2 TO USer1; GO GRANT ALTER ON SCHEMA::[Schema2] TO [USer1] go GRANT CREATE TABLE TO [USer1] go GRANT CREATE PROCEDURE TO [USer1] go

-- Este comando muda o usuário executando os comandos na janela corrente.

EXECUTE AS USER = 'User1'; go

CREATE TABLE schema2.tb (col int) go

INSERT Schema2.tb VALUES (1) GO 5

SELECT * FROM Schema2.tb go

CREATE PROCEDURE schema2.prc as SELECT 1 AS 'CRIAR PROC'; go

EXEC schema2.prc GO

ALTER PROCEDURE schema2.prc as SELECT 'ALTERAR PROC', * FROM Schema2.tb; go

EXEC schema2.prc GO

DROP PROCEDURE schema2.prc go

DROP TABLE schema2.tb GO

-- Estes dois comandos vão dar erro, já que o usuário User1 não tem permissão no schema DBO.

CREATE TABLE dbo.tb (col int) go CREATE PROCEDURE dbo.prc as SELECT 1; go

-- Este comando retorna ao usuário que iniciou a execução deste script.

REVERT GO

-- O usuário atual tem permissões "superiores", então consegue criar as tabelas no schema DBO.

CREATE TABLE dbo.tb (col int) GO CREATE PROCEDURE dbo.prc as SELECT 1; GO

DROP TABLE dbo.tb GO DROP PROCEDURE dbo.prc GO

DROP SCHEMA Schema2; GO

DROP USER User1; GO

Agora não esqueço nunca mais esses comandos. Quero também agradecer ao amigo Rodrigo, do grupo SQL Maniacs, que me corrigiu quando eu postei uma informação incompleta sobre esse assunto no grupo. Um comando errado e meus testes foram invalidados, ainda bem que ele conferiu e me avisou.