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.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Security and access question

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.
Go to Top of Page

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?
Go to Top of Page

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 command

grant all on dbo to (DBusername)
Go to Top of Page
   

- Advertisement -