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 Thursday, October 09, 2008 10:55 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,

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.

Thanks,
Anjan
Post #583485
Posted Thursday, October 09, 2008 12:06 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/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.

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



============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #583521
Posted Monday, October 01, 2012 1:02 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, March 28, 2014 6:16 AM
Points: 21, Visits: 216
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]

Post #1366715
Posted Thursday, July 04, 2013 3:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 3:09 AM
Points: 3, Visits: 20
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
Post #1470353
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse