There is no need to grant CONTROL
on the schema.
The permission required to DROP SCHEMA
is either CONTROL
on the schema or ALTER ANY SCHEMA
at the database level, and that is why your user was able to drop the schema. Removing these two permissions will prevent the role-associated users from creating and droping the schema (unless they have higher level permissions of course).
The required permission to CREATE
ALTER
and DROP
other objects is the CREATE
permission for the object type (table\procedure\function\view) combined with ALTER
permission on the schema.
You already have these permissions in your script, so all that you have to do is remove the CONTROL
permission. For reference, here is a BOL list of DDL
statements where You can find the required permission for all object types.
For the lazy, here is your code after removing the unnecessary permission:
CREATE ROLE myrole AUTHORIZATION dbo;
EXEC sp_addrolemember 'myrole', 'myuser';
CREATE SCHEMA myschema AUTHORIZATION dbo;
GRANT ALTER, DELETE, EXECUTE, INSERT, REFERENCES, SELECT,
UPDATE, VIEW DEFINITION ON SCHEMA::myschema TO myrole;
GRANT CREATE TABLE, CREATE PROCEDURE, CREATE FUNCTION, CREATE VIEW TO myrole;
The basic concept is to use GRANT/DENY Schema Permissions. You can efficiently manage permissions by creating a role and then adding members to it.
Below is an example that will explain you in detail
use master
go
--Create Logins
CREATE LOGIN UserA WITH Password='UserA123';
go
CREATE LOGIN UserB WITH Password='UserB123';
use AdventureWorks2008R2
go
--Create Database Users
CREATE USER UserA;
go
CREATE USER UserB;
go
--Create the Test Schemas
CREATE SCHEMA SchemaA AUTHORIZATION UserA
go
CREATE SCHEMA SchemaB AUTHORIZATION UserB
go
-- create test tables
create table schemaA.TableA (fname char(5))
go
insert into schemaA.TableA (fname) values ('Kin-A')
go
create table SchemaB.TableB (fname char(5))
go
insert into SchemaB.TableB (fname) values ('Kin-B')
go
Now test :
--Test for UserA in SchemaA
EXEC('select * from schemaA.TableA') AS USER = 'UserA'
go
--Kin-A
-- Test for UserB in SchemaB == this should fail
EXEC('select * from SchemaB.TableB') AS USER = 'UserA'
go
--Msg 229, Level 14, State 5, Line 1
--The SELECT permission was denied on the object 'TableB', database 'AdventureWorks2008R2', schema 'SchemaB'.
Now create Stored Procedures :
CREATE PROCEDURE SchemaB.proc_SelectUserB
AS
select * from schemaA.TableA;
go
create procedure schemaA.proc_SchemaA
as
select * from schemaA.TableA
Now Grant execute permissions to UserA on schemaB's SP
GRANT EXECUTE ON OBJECT::[SchemaB].[proc_SelectUserB] TO [UserA]
go
Test it .. to see if UserA is able to run SP from schemaB. This will PASS
EXECUTE AS LOGIN='UserA';
Exec SchemaB.proc_SelectUserB;
revert;
go
--- Kin-A
But UserA wont be able to see data from SchemaB
EXECUTE AS LOGIN='UserA';
select * from SchemaB.TableB
revert;
go
--- Msg 229, Level 14, State 5, Line 3
--- The SELECT permission was denied on the object 'TableB', database 'AdventureWorks2008R2', schema 'SchemaB'.
Alternatively you can use DATABASE ROLE and just add users to it for better manageability of permissions:
EXEC sp_addrole 'SchemaBUsesSchemaAProc'
go
EXEC sp_addrolemember 'SchemaBUsesSchemaAProc','UserA';
go
Below statement will make sure that UserA is able to see schemaA and NOT schemaB. The good thing is that you can just add users to SchemaBUsesSchemaAProc
role and they will inherit all the permissions granted to that role.
GRANT SELECT ON SCHEMA::SchemaA TO SchemaBUsesSchemaAProc;
go
If you only want to allow UserA to execute SP's which are owned by SchemaB then below statement will do the job:
GRANT EXECUTE ON OBJECT::[SchemaB].[proc_SelectUserB] TO [SchemaBUsesSchemaAProc]
go
This way, UserA is not able to see SchemaB's tables, but still can execute procs from SchemaB.
Below will explain the permission hierarchy :
Best Answer
The correct grants are something like:
It's pretty important that you don't grant the user the ability to alter a schema owned by another user, especially one owned by dbo. If you do ownership chains will allow the user to skip permissions checks on the schema owner's objects in all schemas.