Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Deny delete and drop permission to the sql user Expand / Collapse
Author
Message
Posted Monday, October 06, 2008 12:03 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 01, 2011 3:13 AM
Points: 139, 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
Post #581296
Posted Monday, October 06, 2008 2:15 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, February 21, 2014 2:16 PM
Points: 1,259, Visits: 3,417
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/
Post #581361
Posted Tuesday, October 07, 2008 4:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 01, 2011 3:13 AM
Points: 139, 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

Post #581641
Posted Tuesday, October 07, 2008 5:41 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, February 21, 2014 2:16 PM
Points: 1,259, Visits: 3,417
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!


Cheers!
Dugi


============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #581692
Posted Tuesday, October 07, 2008 5:43 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, February 21, 2014 2:16 PM
Points: 1,259, Visits: 3,417
...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/
Post #581694
Posted Tuesday, October 07, 2008 8:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 01, 2011 3:13 AM
Points: 139, Visits: 282
hi dugi

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

any idea how to fix this issue?

Post #581817
Posted Tuesday, October 07, 2008 8:37 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 01, 2011 3:13 AM
Points: 139, Visits: 282
Hi steve

do u have any idea
Post #581876
Posted Wednesday, October 08, 2008 12:50 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, February 21, 2014 2:16 PM
Points: 1,259, Visits: 3,417
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!




============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #582359
Posted Wednesday, October 08, 2008 9:13 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 01, 2011 3:13 AM
Points: 139, 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,
Anjan:)

Post #582680
Posted Wednesday, October 08, 2008 1:04 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Friday, February 21, 2014 2:16 PM
Points: 1,259, Visits: 3,417
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!


============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #582869
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse