can a non-serveradmin be enabled to drop a database?

Question

can a non-serveradmin be enabled to drop a database?

sqlguy-736318

Hall of Fame

Points: 3859
More actions
December 15, 2011 at 2:23 pm

#247823

A Login with the serveradmin server role is allowed to drop a database. Can a Login with a non-serveradmin role be enabled to drop a database?
I have a user who I've configured with a db_owner User Mapping for a database. He may require the ability to rename or drop the database. Can this be enabled without granting the serveradmin server role? I don't want him to have access to other databases....

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

sqlguy-736318 (12/15/2011)
A Login with the serveradmin server role is allowed to drop a database. Can a Login with a non-serveradmin role be enabled to drop a database?
I have a user who I've configured with a db_owner User Mapping for a database. He may require the ability to rename or drop the database. Can this be enabled without granting the serveradmin server role? I don't want him to have access to other databases....

anyone in the role of db_owner can drop the database, since they own it. no need to be any other admin functionality.

to avoid that, you might want to make a rol that is AlmostOwners, that gives them sufficient rights to do stuff int he database, but not the stuff db_owner allows, like backup and drop.

renaming the database, i think requires a higher role than that; but there is a dbcreator role that you can add to his login to allow the creation of new databases,.

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!

Lowell SSC Guru Points: 323518 More actions · Answer 2

BOL says the user needs ALTER permission on the database to rename it:

http://msdn.microsoft.com/en-us/library/ms174269.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!

Lowell SSC Guru Points: 323518 More actions · Answer 3

yep, here's a proof of concepty suite of scripts to test with; worked fine for me:

create login ClarkKent with password = 'NotARealpassword'

EXEC master..sp_addsrvrolemember @loginame = N'ClarkKent', @rolename = N'dbcreator'

EXECUTE AS LOGIN = 'ClarkKent'

--who am i?

SELECt SUSER_NAME()

CREATE DATABASE DELETEME

ALTER DATABASE DELETEME MODIFY NAME = SAVEME

DROP DATABASE SAVEME

REVERT; --turn back into superman

DROP LOGIN ClarkKent

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!

sqlguy-736318 Hall of Fame Points: 3859 More actions · Answer 4

Can you think of any DBA tasks that a db_owner would not be allowed to perform on a database?

Basically, I have 1 server with 2 production databases. The org wants to give full control of 1 of the databases to the guy who writes queries for that database.

However, I don't want the query writer to be able to make any changes that will impact the server or other database.

Can db_owner do db maintenance tasks like execute the db command to rebuild all indexes?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 5

Yes - dbo can rebuild indexes.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events