Take the 2-minute tour ×
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. It's 100% free, no registration required.

Please advice to give permission for a SQL server user. Conditions are, this user must be able to create,delete,modify,drop the tables/view/procedure on user default schema(not dbo) not in any other schema. I have given db_ddladmin privilege but here the user will be able to perform any action on other schema's as well.

share|improve this question

migrated from stackoverflow.com Jun 12 '13 at 12:07

This question came from our site for professional and enthusiast programmers.

2 Answers 2

GRANT CONTROL ON SCHEMA :: BobsSchema To Bob;

Where Bob is the users name. See MSDN.

share|improve this answer
    
Thanks for the reply. But here the user Bob will be able to edit/modify/alter the tables of other users as well. I have to restrict the permission to one schema. –  user2477113 Jun 12 '13 at 6:35
    
@user2477113 - sorry, I initially read it as dbo, but you can switch it to name whatever other schema it is that you want them to have access to. –  Damien_The_Unbeliever Jun 12 '13 at 6:37

If Bob is the owner of BobSchema then you don't need to set any permissions at all - object owners have full control over their objects.

Additionally, If Bob is the owner of BobSchema then Bob will by default be the owner of all objects created in BobSchema (in SQL Server 2005+).

So, one way to solve your situation is to create the schemas using something like create schema BobSchema authorization Bob;.

share|improve this answer

Your Answer

 
discard

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