2

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?

2

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.

  • I returned the error: Permission CREATE TABLE denied in database xxxx – Douglas Oct 5 '15 at 17:38
  • @Douglas You need to provide more info if you want troubleshooting help. For example, what is the exact statement that generated that error? What permissions have you given to that user? What was the exact statement you used to grant those permissions? – Aaron Bertrand Oct 5 '15 at 17:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.