Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm very green when it comes to the world of database permissions management in SQL Sever.

Let's keep an example simple.

Say account 'admin' is the owner of schemas A, B, and C.

There another account 'minion' that you want to have full rights (update/delete/insert/select/alter) on any object (table/view) created under schemas A, B, and C.

Is this possible? Or do you have to execute a grant statement each and every time you add a table/ view under these schemas? (seems a bit silly to me).

share|improve this question
up vote 4 down vote accepted

You can GRANT schema permissions that are effective for everything existing and everything that will exist in that schema.

Grant Schema Permissions

GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA :: <schema> TO <user>;

Further to that, if you want to then deny permissions on a certain object within that schema, you can do.

Denying Object Permissions

DENY INSERT ON OBJECT::<schema>.<object> TO <user>;
share|improve this answer

To simplify a bit further, you can use roles to do the job that you are looking for.

Once you assign permissions to the role, you can just add users to the role. This way you dont have to manage permissions for individual users. The users inherit permissions granted to role.

Below is an example to get you started :

 -- Create the database role
CREATE ROLE TableSelector AUTHORIZATION [dbo]
GO
 ---- Grant access rights to a specific schema in the database
GRANT 
      SELECT, INSERT, UPDATE, DELETE, ALTER 

ON SCHEMA::dbo
      TO TableSelector 
GO

-- Add an existing user to the new role created 
EXEC sp_addrolemember 'TableSelector', 'MyDBUser'
GO

-- Revoke access rights on a schema from a role 
DENY ALTER  -- you can customize here ...
ON SCHEMA::dbo
      TO TableSelector 
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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