InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 16:43:44 GMT20http://www.sqlservercentral.com/Forums/Topic581296-359-1.aspxHi, i have similar problem sometime ago. I have a user that need to be dbo for some reason but i neet to limited to "no drop".i found the solution using [code="sql"]DENY ALTER ON SCHEMA::[dbo] to [someuser][/code]Mon, 01 Oct 2012 13:02:13 GMTSeed Vicioushttp://www.sqlservercentral.com/Forums/Topic581296-359-1.aspx[quote][b]anjan.ashok (10/9/2008)[/b][hr]Hi dugi,If i grant create table and alter permission, then the user can drop the table i have crosschecked, but if deny createtable and alter table then the user can't drop table.So how fix this.:hehe:Thanks,Anjan[/quote]You have confusion attack, try from begining create the user then add datawriter role then run this script:[code]use [Your_Database]GOGRANT INSERT TO [Your_User]GOuse [Your_Database]GOGRANT SELECT TO [Your_User]GOuse [Your_Database]GOGRANT UPDATE TO [Your_User]GOuse [Your_Database]GODENY DELETE TO [Your_User]GOuse [Your_Database]GOGRANT ALTER TO [Your_User]GOuse [Your_Database]GOGRANT CREATE TABLE TO [Your_User]GOuse [Your_Database]GO[/code]Thu, 09 Oct 2008 12:06:30 GMTDugihttp://www.sqlservercentral.com/Forums/Topic581296-359-1.aspxHi dugi,If i grant create table and alter permission, then the user can drop the table i have crosschecked, but if deny createtable and alter table then the user can't drop table.So how fix this.:hehe:Thanks,AnjanThu, 09 Oct 2008 10:55:55 GMTanjan.ashokhttp://www.sqlservercentral.com/Forums/Topic581296-359-1.aspx[quote][b]anjan.ashok (10/8/2008)[/b][hr]hi Dugi,Thanks buddyi added to datawriter role and now drop permission is denied.One more question?When i tried to create table ,i am getting following errorMsg 2760, Level 16, State 1, Line 1The specified schema name "dbo" either does not exist or you do not have permission to use it.when i tried to alter table i am getting following error Msg 1088, Level 16, State 13, Line 1Cannot find the object "test1" because it does not exist or you do not have permissions.To fix can i assign permissions at the database level or can i do through grant permission i guess its not possible through grant querypls help me on this.Thanks,Anjan:)[/quote]Ok you have also these options:[code]use [TEST]GOGRANT ALTER TO [Your_User]GOuse [TEST]GOGRANT CREATE TABLE TO [Your_User]GOuse [TEST]GOGRANT CREATE VIEW TO [Your_User]GO[/code]The last one is also to create the view!Wed, 08 Oct 2008 13:04:26 GMTDugihttp://www.sqlservercentral.com/Forums/Topic581296-359-1.aspxhi Dugi,Thanks buddyi added to datawriter role and now drop permission is denied.One more question?When i tried to create table ,i am getting following errorMsg 2760, Level 16, State 1, Line 1The specified schema name "dbo" either does not exist or you do not have permission to use it.when i tried to alter table i am getting following error Msg 1088, Level 16, State 13, Line 1Cannot find the object "test1" because it does not exist or you do not have permissions.To fix can i assign permissions at the database level or can i do through grant permission i guess its not possible through grant querypls help me on this.Thanks,Anjan:)Wed, 08 Oct 2008 09:13:33 GMTanjan.ashokhttp://www.sqlservercentral.com/Forums/Topic581296-359-1.aspx[quote][b]anjan.ashok (10/7/2008)[/b][hr]hi dugione problem delete permission is restricted but the testuser can drop the table any idea how to fix this issue?[/quote]Maybe you didn't run the script in correct and can you tell me you user, member of what role is!?Try that your user to be the member of "db_datawriter" role!Here is the commands:[code]DELETE PAYROLLWHERE No = 2;MSG:Msg 229, Level 14, State 5, Line 1The DELETE permission was denied on the object 'Payroll', database 'TEST', schema 'dbo'.[/code]And[code]DROP TABLE PAYROLL;GOMSG:Msg 3701, Level 14, State 20, Line 4Cannot drop the table 'Payroll', because it does not exist or you do not have permission.[/code]For me works fine and everything is correct!:w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t:Wed, 08 Oct 2008 00:50:13 GMTDugihttp://www.sqlservercentral.com/Forums/Topic581296-359-1.aspxHi steve do u have any ideaTue, 07 Oct 2008 08:37:25 GMTanjan.ashokhttp://www.sqlservercentral.com/Forums/Topic581296-359-1.aspxhi dugione problem delete permission is restricted but the testuser can drop the table any idea how to fix this issue?Tue, 07 Oct 2008 08:01:29 GMTanjan.ashokhttp://www.sqlservercentral.com/Forums/Topic581296-359-1.aspx...I forgot for the type of user... the first post it was for SQL Server user so if you have domain users you should write like this for the user "Domain\Your_User"Tue, 07 Oct 2008 05:43:01 GMTDugihttp://www.sqlservercentral.com/Forums/Topic581296-359-1.aspxhmmm...did you try to drop any object after the script above ...ok make a test go create TEST db then create 3-4 table then apply again the script that I posted above and I'm sure that you can not drop = delete any object!Finally I can see you that you didn't try to delete ...ok don't do it in production server do some test before i told you to create one simple db with 3-4 tables! :w00t::w00t::w00t::w00t:Cheers!DugiTue, 07 Oct 2008 05:41:56 GMTDugihttp://www.sqlservercentral.com/Forums/Topic581296-359-1.aspxHi dugi,Thanks for the replynow u have restricted for delete same way can i restrict for drop permission also rightuse Databasegodeny drop to userThanks,AnjanTue, 07 Oct 2008 04:05:42 GMTanjan.ashokhttp://www.sqlservercentral.com/Forums/Topic581296-359-1.aspxuse [Your_Database]GOGRANT INSERT TO [Your_User]GOuse [Your_Database]GOGRANT SELECT TO [Your_User]GOuse [Your_Database]GOGRANT UPDATE TO [Your_User]GOuse [Your_Database]GODENY DELETE TO [Your_User]GOYour_User --- is user that you want grant select insert & update but not to drop or delete!Mon, 06 Oct 2008 14:15:18 GMTDugihttp://www.sqlservercentral.com/Forums/Topic581296-359-1.aspxHi,how to deny delete and drop permission to the sql user for a particular database in sql server 2005I tried with roles and other permissioni am not able to deny both delete and drop permission.Only delete and drop permission has to be denied rest update,insert and alter permissions should be givenPls help meMon, 06 Oct 2008 12:03:49 GMTanjan.ashok