Grant ALL & Deny Delete

Question

Grant ALL & Deny Delete

rew-370421

Hall of Fame

Points: 3640
More actions
January 13, 2010 at 4:34 pm

#215420

I need to enable a User (in development) to basically do everything within a database (create tables, SPs, Views etc., as well as Select, Insert, Updates). Everything EXCEPT "DELETE".
So if I assign them db_owner, is it possible to then do a REVOKE or DENY of some kind so they can not do DELETEs of data as well as Deleting a Table or View etc. ?

Viewing 11 posts - 1 through 10 (of 10 total)

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

Lowell SSC Guru Points: 323482 More actions · Answer 1

i wouldn't give them the db-owner role, but instead create a role something like the following:

remember the role of db_owner has the ability to drop the database, which you probably don't want them to have, even though noone would abuse it.

CREATE ROLE PowerUsers

ALTER AUTHORIZATION ON SCHEMA::[db_ddladmin] TO [PowerUsers] --make procs and stuff

ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [PowerUsers] --read

ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [PowerUsers] --write and delete

GRANT EXECUTE,ALTER TO [PowerUsers] --execute procs views functions

DENY DELETE TO [PowerUsers] --remove delete

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!

rew-370421 Hall of Fame Points: 3640 More actions · Answer 2

Thanks Lowell.

I created a Database Role via your recommendations and added a User to it. I've just begun testing and I'm able to DELETE a record from Tables. So the DENY is not working.

Any ideas?

Daveee SSCommitted Points: 1570 More actions · Answer 3

Is the user in any database roles eg.db_datawriter or fixed server roles that could be overriding this?

rew-370421 Hall of Fame Points: 3640 More actions · Answer 4

yes, the User is in DB_DDLADMIN, DB_DATAREAD & DB_DATAWRITER. I thought if I do the "DENY DELETE" command at the end that it would take away that privilege. If that's not correct, how can I allow the User to CREATE, ALTER etc as well as UPDATE, SELECT & INSERT Data without allowing them to DELETE data and Tables, Views etc?

Or is what I'm trying to do not possible?

thanks!

Daveee SSCommitted Points: 1570 More actions · Answer 5

I might have even confused myself. I think that a DENY should always take precedence over any other permissions.

However I think fixed server roles will override database level permissions. The users isn't in the 'sa' fixed server role by any chance?

rew-370421 Hall of Fame Points: 3640 More actions · Answer 6

I seem to have read, too, that a DENY will NOT OVERRIDE a Fixed Server Role Permissions. But, for the life of me I can not find the Microsoft MSDN to confirm that! To answer your other question, the User is NOT a member of the "sa" fixed server role.

Any help is appreciated. This is driving me nuts!

Lowell SSC Guru Points: 323482 More actions · Answer 7

rew can you login as that specific user in SSMS, and run these two queries? maybe the user has other roles or rights?

select * from fn_my_permissions(null,'SERVER')

select * from fn_my_permissions(null,'DATABASE')

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!

rew-370421 Hall of Fame Points: 3640 More actions · Answer 8

Here are the results from your questions:

serverCONNECT SQL

serverVIEW ANY DATABASE

databaseCREATE TABLE

databaseCONNECT

In the meantime, I'm doing a slow process of trial and error.

The User is a member of a Database Role I created called "PowerUsers". Within "PowerUsers" I gave the "DBO" schema (select, update & insert), NOT "delete". This seems to work when doing DML commands.

I'll try the same thing with EXECUTE, too.

Now I'm trying to give it rights to CREATE Tables, so I did this:

"GRANT CREATE TABLE TO [PowerUsers];"

Then when I tried to create a TABLE logged in as the User, I get this error:

Server: 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.

I was hoping there would be an easier way.

rew-370421 Hall of Fame Points: 3640 More actions · Answer 9

This is frustrating. To this novice's mind, I can NOT DENY a User's or Database Role's ability to DROP TABLEs if I GRANT them ALTER (which appears to be needed in order for them to CREATE TABLEs).

In short,

- at a DML level, I can deny them DELETE of data and still allow them to SELECT, INSERT, UPDATE

- at an OBJECT level, I can allow them to CREATE TABLEs, but I can NOT DENY/REVOKE the right to DROP them.

bummer :crazy:

Lowell SSC Guru Points: 323482 More actions · Answer 10

rew i think you are right...when you grant someone ALTER, the can alter or drop the object...as they might need to drop in order to create/recreate

i've got a database DDL trigger to prevent DROP_PROCEDURE, in my snippets to prevent that situation;

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!