Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Deny delete and drop permission to the sql user


Deny delete and drop permission to the sql user

Author
Message
anjan.ashok
anjan.ashok
SSC-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 Visits: 282
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
Dugi
Dugi
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1316 Visits: 3511
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!

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/
anjan.ashok
anjan.ashok
SSC-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 Visits: 282
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
Dugi
Dugi
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1316 Visits: 3511
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!
w00tw00tw00tw00t

Cheers!
Dugi

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Dugi
Dugi
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1316 Visits: 3511
...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"

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/
anjan.ashok
anjan.ashok
SSC-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 Visits: 282
hi dugi

one problem delete permission is restricted but the testuser can drop the table

any idea how to fix this issue?
anjan.ashok
anjan.ashok
SSC-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 Visits: 282
Hi steve

do u have any idea
Dugi
Dugi
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1316 Visits: 3511
anjan.ashok (10/7/2008)
hi dugi

one 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!


w00tw00tw00tw00tw00tw00tw00tw00t

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/
anjan.ashok
anjan.ashok
SSC-Enthusiastic
SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)SSC-Enthusiastic (141 reputation)

Group: General Forum Members
Points: 141 Visits: 282
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,
AnjanSmile
Dugi
Dugi
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1316 Visits: 3511
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,
AnjanSmile



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!

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search