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 |
|
e106199
Starting Member
13 Posts |
 Posted - 2005-11-17 : 19:31:34
|
| Hi all,i am trying to create a database user who has access to a database but not full access. I want him to be able to write (create new tables, stored procedures, views etc) but i want to restrict his rights over some tables. If i have 10 tables i want him to access 6 of them and not be able to touch the rest 4 or anything that is using one of these 4 tables. If there is a view that uses on of these restricted tables i want it to be not accessible to the user also, same for stored procedures etc.I made the user the owner of database and denied access over a table on select/insert/update/delete/dri. Being the db owner lets him create new tables but the problem is he can change the design of the table that he is supposed to be restricted. He cant see the content but he can change its design. Then i created a view that uses the table he s denied teh above functions, and he s able to view or design it also.What i want is a full restriction over some items. If he s restricted over a table he shoudnt be able to see or change it or any item that uses the table. |
|
|
surendrakalekar
Posting Yak Master
120 Posts |
Posted - 2005-11-18 : 02:02:36
|
| db_owner has all permissions in the database. Insted of db_owner try db_ddladmin/db_datareader/db_datawriter.Surendra |
 |
|
|
e106199
Starting Member
13 Posts |
Posted - 2005-11-18 : 08:17:02
|
| db_ddladmin/db_datareader/db_datawrites has almost db_owner rights. i need to create a custom role but i dont know how to give user the right to create new tables or edit the ones i assign him to. -shane |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-11-20 : 22:30:52
|
| create an application for him to use instead of doing it on EM or QA--------------------keeping it simple... |
|
|
|
e106199
Starting Member
13 Posts |
Posted - 2005-11-20 : 22:38:12
|
| what do you mean create an application?-shane |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-11-20 : 23:13:42
|
create a program/software/application/user interface for him to use instead of doing what he needs to be doing in enterprise manager or query analyzer --------------------keeping it simple... |
|
|
|
surendrakalekar
Posting Yak Master
120 Posts |
Posted - 2005-11-21 : 01:26:09
|
quote:
Originally posted by e106199
db_ddladmin/db_datareader/db_datawrites has almost db_owner rights. i need to create a custom role but i dont know how to give user the right to create new tables or edit the ones i assign him to. -shane
You can create your own Role & Rule in SQL Server. Read online help on these objects and find out 'Security Procedures' in 'System Stored Procedures'. There you will find out the stored procedure to add Role and Rule.Surendra |
|
|
|
|
|
|
Restricting user rights over a table