DENY DROP TABLE & ALTER TABLE Error

Question

Post reply

DENY DROP TABLE & ALTER TABLE Error

Welsh Corgi

SSC Guru

Points: 116520
More actions
October 11, 2012 at 7:22 am

#393363

I'm able to DENY CREATE TABLE with the following Command:
DENY CREATE TABLE TO [DomainName\UserName]
I get an error when attempting to DENY DROP TABLE?
DENY DROP TABLE TO [DomainName\UserName]
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'DROP'.
I also get an error when I attempt to DENY ALTER TABLE?
DENY ALTER TABLE TO [DomainName\UserName]
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'ALTER'.
I goggled a lot and I can't determine what I'm doing wrong?
Any help would be appreciated.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 1

I also need to DENY ALTER SCHEMA but I'm getting an error.

Does anyone know the correct syntax?

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 2

What permission does that user have currently?? Is that user the owner of that table??

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 3

Ratheesh.K.Nair (10/11/2012)
What permission does that user have currently?? Is that user the owner of that table??

They are members of the fixed database role db_owner.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 4

If you want to DENY Create,Drop & Alter why cant you give a less privilege to that user??

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 5

A couple of things to try:

(1) Use DELETE instead of DROP

(2) Add an ON clause (although I don't know why that would be needed for DENY DELETE/ALTER but not for DENY CREATE).

Also, if the user is db_owner, doesn't that override any permissions in the database?

John

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 6

Ratheesh.K.Nair (10/11/2012)
If you want to DENY Create,Drop & Alter why cant you give a less privilege to that user??

I had then as DDL Admin but there is a bug with that and you get an error when you try to use Table Design.

They need to be able to create, alter, drop Stored Procedures, Functions and views in certain schema.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 7

Ratheesh.K.Nair (10/11/2012)
What permission does that user have currently?? Is that user the owner of that table??

No, I'm the owner of all objects.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

VastSQL SSC-Dedicated Points: 36097 More actions · Answer 8

Welsh Corgi (10/11/2012)
Ratheesh.K.Nair (10/11/2012)
If you want to DENY Create,Drop & Alter why cant you give a less privilege to that user??
I had then as DDL Admin but there is a bug with that and you get an error when you try to use Table Design.
They need to be able to create, alter, drop Stored Procedures, Functions and views in certain schema.

Better create a role with permissions to create,alter & drop SP and add that role to user.

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 9

John Mitchell-245523 (10/11/2012)
A couple of things to try:
(1) Use DELETE instead of DROP
(2) Add an ON clause (although I don't know why that would be needed for DENY DELETE/ALTER but not for DENY CREATE).
Also, if the user is db_owner, doesn't that override any permissions in the database?
John

Could you please expand on your comments about DELETE and the ON Clause?

Deny permission overide all permissions granted explicitly or implicitly through a role.

I was able to DENY CREATE TABLE to two users and it worked.

I'm sysadmin and I could DENY Read Permissions and it would work.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 10

Welsh Corgi (10/11/2012)
Could you please expand on your comments about DELETE and the ON Clause?

Yes - try DENYing DELETE or ALTER on the schema that the tables are in.

Deny permission overide all permissions granted explicitly or implicitly through a role.

Yes, I think you're right. I must have been thinking of the dbo user.

I was able to DENY CREATE TABLE to two users and it worked.

Indeed. Like I said, I don't know why the rules might be different for DENY CREATE and DENY ALTER/DELETE.

I'm sysadmin and I could DENY Read Permissions and it would work.

Read permission? I think the rules for DDL and DML permissions might be slightly different.

John

Lowell SSC Guru Points: 323518 More actions · Answer 11

this thread has a very similar issue:

too strong of permissiosn were granted, but the DENY command doesn't have the level of granularity to take away things like ALTER TABLE

this example was where the original postered wanted to only allow CREATE/ALTER view permissions, and i thought a DDL trigger would be the only solution.

this might help:

http://www.sqlservercentral.com/Forums/Topic1241211-391-1.aspx

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 12

Ratheesh.K.Nair (10/11/2012)
Welsh Corgi (10/11/2012)
Ratheesh.K.Nair (10/11/2012)
If you want to DENY Create,Drop & Alter why cant you give a less privilege to that user??
I had then as DDL Admin but there is a bug with that and you get an error when you try to use Table Design.
They need to be able to create, alter, drop Stored Procedures, Functions and views in certain schema.
Better create a role with permissions to create,alter & drop SP and add that role to user.

No, not in SQL Server 2008.

If I did what you said they would get an error in Table design. I got a bunch of complaints.

Look at the following article. Perhaps you know of a tested work around?

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=159031

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 13

John Mitchell-245523 (10/11/2012)
Welsh Corgi (10/11/2012)
Could you please expand on your comments about DELETE and the ON Clause?
Yes - try DENYing DELETE or ALTER on the schema that the tables are in.
I was able to DENY CREATE TABLE to two users and it worked.
Indeed. Like I said, I don't know why the rules might be different for DENY CREATE and DENY ALTER/DELETE.
John

I do not want to DENY DELETE, they should have that privilege.

I have tried DENY ALTER SCHEMA but I got an error. I listed the eary in my second post.

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 14

Not DENY ALTER SCHEMA. Something like DENY ALTER ON SCHEMA::SchemaName. You'd have to check out the exact syntax yourself if that isn't quite correct.

John