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.


Where Bob is the users name. See MSDN.

| 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;.

| improve this answer | |

Your Answer

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