Deny the 'drop database' right to a user with dbcreator server role

Question

Post reply

Deny the 'drop database' right to a user with dbcreator server role

Schadenfreude-Mei

SSChampion

Points: 14083
More actions
May 22, 2009 at 2:09 am

#130416

Hey guys,
Hopefully a quickie.
I hahve a user who wants to restore databases from a specific folder.
I've got most of it figured but want to revoke the 'dbcreator' access of allowing database deletion.
DENY DELETE TO {user} - doesnt work.
Advise please 🙂
Adam Zacks-------------------------------------------Be Nice, Or Leave

Viewing 6 posts - 1 through 5 (of 5 total)

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

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 1

Joy Smith San

SSC-Insane

Points: 24877

May 22, 2009 at 2:12 am

#998717

Allocate the rights to "ROLE", not user.

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 2

Thanks for your reply.

Can you explain what you mean?

Adam Zacks-------------------------------------------Be Nice, Or Leave

Lowell SSC Guru Points: 323518 More actions · Answer 3

something like this example might help.

I'm creating 3 roles...one that is almost-but-not-quite-admin, one for regular users, and a third to explicity deny deletion rights.

i then assign various users to those roles so that together, they give or take away the permissions i want them to have.

--create the Role for my Dev guys

CREATE ROLE [WhateverDEVAdmins]

ALTER AUTHORIZATION ON SCHEMA::[db_ddladmin] TO [WhateverDEVAdmins]

ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [WhateverDEVAdmins]

ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [WhateverDEVAdmins]

--create role for my normal users

CREATE ROLE [WhateverDEVUsers]

ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [WhateverDEVUsers]

ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [WhateverDEVUsers]

--assign my two admins to my more powerful role

EXEC sp_addrolemember N'WhateverDEVAdmins', N'bob'

EXEC sp_addrolemember N'WhateverDEVAdmins', N'jeff'

--assign my ordinary role to the login for network users

EXEC sp_addrolemember N'WhateverDEVUsers', N'NT AUTHORITY\Authenticated Users'

--create the Role to prevent deletions

CREATE ROLE [WhateverPreventDeletions]

--prevent my almost-admins from deleting data

DENY DELETE TO [WhateverPreventDeletions]

EXEC sp_addrolemember N'WhateverPreventDeletions', N'bob'

EXEC sp_addrolemember N'WhateverPreventDeletions', N'jeff'

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!

Schadenfreude-Mei SSChampion Points: 14083 More actions · Answer 4

Hey Lowell,

Cool avatar btw. I have already tried DENY DELETE TO {user} and it doesnt work (my guess is that for some reason the DBCREATOR server role overwrites it}. Any other ideas?

Basically all the user needs to do is restore one database withe the same backup file over and over again...

Thought this would be simple(s) 🙁

Adam Zacks-------------------------------------------Be Nice, Or Leave

Lowell SSC Guru Points: 323518 More actions · Answer 5

doh i'm sorry; i jumped to conclusions and thought the question was "how to assign/deny permissions to a role" ; Lemme test a couple of thoeries and i'll post the results.

I belive I have an example of a "normal" user calling a stored procedure, and the stored procedure (in master) runs with EXECUTE AS for elevated permissions to restore a database.

I need to make sure it still works, but that was how I did it before....so the end user never had dbowner/creator rights at all.

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!