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;
Only users mapped to a login that's a member of the sysadmin fixed server role can create and alter an assembly with an UNSAFE permission set defined. Therefore, to achieve your desired result you would have to add that respective login to the sysadmin fixed server role.
BOL reference on CREATE ASSEMBLY:
Specifying UNSAFE
enables the code in the assembly complete freedom to perform operations in the SQL Server process space that can potentially compromise the robustness of SQL Server. UNSAFE
assemblies can also potentially subvert the security system of either SQL Server or the common language runtime. UNSAFE
permissions should be granted only to highly trusted assemblies. Only members of the sysadmin fixed server role can create and alter UNSAFE
assemblies.
Best Answer
to grant use privliges to create database you can use the following command:
To drop it you need to make sure the user has owner privileges on the database, you can use the command
ALTER AUTHORIZATION ON DATABASE::db TO [LoginName];