Sql-server – Permission in schema for user

permissionsschemaSecuritysql serversql server 2014

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?

Best Answer

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.

Related Question