October 6, 2008 at 12:03 pm
Hi,
how to deny delete and drop permission to the sql user for a particular database in sql server 2005
I tried with roles and other permission
i 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 given
Pls help me
October 6, 2008 at 2:15 pm
use [Your_Database]
GO
GRANT INSERT TO [Your_User]
GO
use [Your_Database]
GO
GRANT SELECT TO [Your_User]
GO
use [Your_Database]
GO
GRANT UPDATE TO [Your_User]
GO
use [Your_Database]
GO
DENY DELETE TO [Your_User]
GO
Your_User --- is user that you want grant select insert & update but not to drop or delete!
October 7, 2008 at 4:05 am
Hi dugi,
Thanks for the reply
now u have restricted for delete same way can i restrict for drop permission also right
use Database
go
deny drop to user
Thanks,
Anjan
October 7, 2008 at 5:41 am
hmmm...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!
Dugi
October 7, 2008 at 5:43 am
...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"
October 7, 2008 at 8:01 am
hi dugi
one problem delete permission is restricted but the testuser can drop the table
any idea how to fix this issue?
October 7, 2008 at 8:37 am
Hi steve
do u have any idea
October 8, 2008 at 12:50 am
anjan.ashok (10/7/2008)
hi dugione problem delete permission is restricted but the testuser can drop the table
any idea how to fix this issue?
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:
DELETE PAYROLL
WHERE No = 2;
MSG:
Msg 229, Level 14, State 5, Line 1
The DELETE permission was denied on the object 'Payroll', database 'TEST', schema 'dbo'.
And
DROP TABLE PAYROLL;
GO
MSG:
Msg 3701, Level 14, State 20, Line 4
Cannot drop the table 'Payroll', because it does not exist or you do not have permission.
For me works fine and everything is correct!
:w00t::w00t::w00t::w00t::w00t::w00t::w00t::w00t:
October 8, 2008 at 9:13 am
hi Dugi,
Thanks buddy
i added to datawriter role and now drop permission is denied.
One more question?
When i tried to create table ,i am getting following error
Msg 2760, Level 16, State 1, Line 1
The 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 1
Cannot 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 query
pls help me on this.
Thanks,
Anjan:)
October 8, 2008 at 1:04 pm
anjan.ashok (10/8/2008)
hi Dugi,Thanks buddy
i added to datawriter role and now drop permission is denied.
One more question?
When i tried to create table ,i am getting following error
Msg 2760, Level 16, State 1, Line 1
The 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 1
Cannot 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 query
pls help me on this.
Thanks,
Anjan:)
Ok you have also these options:
use [TEST]
GO
GRANT ALTER TO [Your_User]
GO
use [TEST]
GO
GRANT CREATE TABLE TO [Your_User]
GO
use [TEST]
GO
GRANT CREATE VIEW TO [Your_User]
GO
The last one is also to create the view!
October 9, 2008 at 10:55 am
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
October 9, 2008 at 12:06 pm
anjan.ashok (10/9/2008)
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
You have confusion attack, try from begining create the user then add datawriter role then run this script:
use [Your_Database]
GO
GRANT INSERT TO [Your_User]
GO
use [Your_Database]
GO
GRANT SELECT TO [Your_User]
GO
use [Your_Database]
GO
GRANT UPDATE TO [Your_User]
GO
use [Your_Database]
GO
DENY DELETE TO [Your_User]
GO
use [Your_Database]
GO
GRANT ALTER TO [Your_User]
GO
use [Your_Database]
GO
GRANT CREATE TABLE TO [Your_User]
GO
use [Your_Database]
GO
October 1, 2012 at 1:02 pm
Hi, 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 DENY ALTER ON SCHEMA::[dbo] to [someuser]
July 4, 2013 at 3:13 am
how to give permission to create table and deny drop;
DENY ALTER ON SCHEMA::DBO TO username -- if we aplly this then we cannot create table.
i need to give permission to create table and deny drop
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply