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 |
|
sqldba20
Posting Yak Master
183 Posts |
 Posted - 2008-10-02 : 10:06:40
|
| Is it possible restrict DROP PROCEDURE/VIEW/FUNCTIONS/TABLES but still give permissions for CREATE and ALTER? Thanks ! |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-02 : 10:08:58
|
| Yes, Unless you give control permission ,they won't be able to drop but can do rest. |
 |
|
|
sqldba20
Posting Yak Master
183 Posts |
Posted - 2008-10-02 : 10:46:19
|
| Here is the error I am getting:The specified schema name "dbo" either does not exist or you do not have permission to use it.I created a user 'test123', granted 'dataread' and 'datawrite' access. At the database level granted 'create procedure' access to this user. To avoid the above mentioned error, I added the User to Schema 'dbo' with only 'ALTER' Access (NO Control access). By doing so, I was able to create,alter procedure but also was able to drop the procedure which I don't want.Thanks, |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-10-02 : 12:51:04
|
| You got this error because you have to give alter permission on schema as well which will let user to drop also.You can following things:1)Let sysadmin create objects2) Give Alter Permissions,View definition so users can modify.Or1) If you do above, You have to create DDL triggers to prevent from happening . |
|
|
|
|
|
|
Security