I created a default database and created some schemas within this database. Created users for each schema, now I want to give full permission for each user in your schema only, how should I do?
From the documentation (GRANT Schema Permissions):
GRANT SELECT, DELETE -- , etc. ON SCHEMA::schema_name TO user_name;
For certain permissions they need to be granted at both the database level and the schema level. For example, you can give a user
CREATE TABLE permissions at the database level, but that doesn't just allow them to create tables in any schema. Here is an example you can work through:
-- create a server-level SQL auth login CREATE LOGIN DouglasExample WITH PASSWORD = 'x', CHECK_POLICY = OFF; -- create an empty database and add a schema CREATE DATABASE Douglas; GO USE Douglas; GO CREATE SCHEMA foo; GO -- create a user linked to the login CREATE USER DouglasExample FROM LOGIN DouglasExample; GO -- grant the user the ability to create tables GRANT CREATE TABLE TO DouglasExample; GO -- grant them explicit permissions on the schema only GRANT SELECT, INSERT, DELETE, ALTER, EXECUTE, CONTROL ON SCHEMA::foo TO DouglasExample; GO -- now try to create tables in foo and in dbo EXECUTE AS USER = N'DouglasExample'; GO CREATE TABLE foo.what(id INT); -- succeeds GO CREATE TABLE dbo.who(id INT); -- fails, not in schema foo -- CREATE TABLE who(id INT); would also fail with same error -- unless DouglasExample had foo as their default_schema GO REVERT; GO
The attempt in dbo fails with:
Msg 2760, Level 16, State 1
The specified schema name "dbo" either does not exist or you do not have permission to use it.
-- clean up: USE master; GO DROP DATABASE Douglas; GO DROP LOGIN DouglasExample;
As a side note, please observe how important it is to always explicitly state the schema name when creating/referencing all objects.