Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
Bill_C
Constraint Violating Yak Guru
299 Posts |
Posted - 2011-05-26 : 09:56:01
|
I have a user (say user1) who wants full access to a schema (say schema1) to be able to create objects and manipulate them within that schema.I don't want that user to be able to see or use any data in any of the other schemas within the database (except maybe dbo.)There are many other databases on the server and i don't want the user to be able to access ANY of the other databases.What access should i give the user at server level?How do I achieve the above?Thanks.wd |
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-05-26 : 10:14:35
|
At the server level just a login - make sure not sysadmin.Just give a mapping to a user in the database (and make it the default) and don't give public access to any other.That stops them accessing anything outside the database.In the database just give them access to the schema==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
Bill_C
Constraint Violating Yak Guru
299 Posts |
Posted - 2011-05-27 : 02:36:40
|
Thanks,Will they then be able to create objects in the schema? |
 |
|
InfraDBA
Starting Member
38 Posts |
Posted - 2011-05-27 : 08:36:50
|
You can change the context of the database to the one where u have the user and then execute the following commandgrant all on dbo to (DBusername) |
 |
|
|
|
|