November 5, 2009 at 8:07 am
Can a user with Read/Write/ddl_admin previalges drop the database ?
November 5, 2009 at 8:13 am
Tara-1044200 (11/5/2009)
Can a user with Read/Write/ddl_admin previalges drop the database ?
No, you should get an Execute permission denied on the object 'sp_delete_backuphistory', database 'msdb'
This has been tested on my own test server with a test login and test database. Please do your own testing. no liabilities or responsibility accepted for listening to my answers 😛
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
November 5, 2009 at 8:18 am
Can a User with dbcreator server role drop the databse ?
November 5, 2009 at 8:24 am
Tara looks like no, can t drop...only with db_owner rights;
here's the code i tried, line by line, and it fails when i tried to dropt eh database as the test user, but when i revert back, to my sa role, o could:
--Can a user with Read/Write/ddl_admin previalges drop the database ?
CREATE DATABASE TestingDropPrivs
create login test with password='test123'
--add to master
use master
create user utest from login test
use TestingDropPrivs
create user utest from login test
--add user to some roles to see what happens
EXEC sp_addrolemember N'db_ddladmin', N'utest'
EXEC sp_addrolemember N'db_datareader', N'utest'
EXEC sp_addrolemember N'db_datawriter', N'utest'
use master
execute as user='utest' --changing to this user
--check security context
print user_name()
drop database TestingDropPrivs --failed no permission
revert; --change back myself
drop database TestingDropPrivs
drop user utest
drop login test
Lowell
November 5, 2009 at 8:28 am
Yes, the thing i negelected to mention, is that with ddl_admin and the other permissions, if uou uncheck the option to remove the backup history. you will get a permissions error when trying to issue the drop database command, instead of the execute denied on the procedure used to delete the backup history.
This has been tested on my own test server with a test login and test database. Please do your own testing. no liabilities or responsibility accepted for listening to my answers
with db_creator, you can drop the database, this is a server role, and not one you would normally give to people.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
November 5, 2009 at 8:36 am
I am still confused how can a user with Read/Write/ddl_admin and dbcreator role deleted a production database. How can that happen though he is not owner of that database ?
If a dbcreator can drop the database how can i restrict him to drop but can create the database.
November 5, 2009 at 9:26 am
Tara-1044200 (11/5/2009)
I am still confused how can a user with Read/Write/ddl_admin and dbcreator role deleted a production database. How can that happen though he is not owner of that database ?If a dbcreator can drop the database how can i restrict him to drop but can create the database.
To be honest I dont think you can, to be honest you shouldnt be giving that sort of access out. to stop databases being dropped, you would need to deny alter any database to the login, but that would stop that login from creating databases at a very minimum.
from bol
dbcreator
Members of the dbcreator fixed server role can create, alter, drop, and restore any database.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
November 5, 2009 at 9:30 am
I am looking for a work around. For sure i have to give them creator role but have to restrict them from dropping.
THough the user is not a owner of that database how could he delete the database?
November 5, 2009 at 9:33 am
Tara, one thing I will point out, are you aware that you can grant create database to a login, you dont have to give them the server role dbcreator
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
November 5, 2009 at 9:34 am
read my previous replies.
dbcreator allows the dropping of any databases
Whatever rights are needed, can be granted without giving the server level role.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
November 5, 2009 at 10:03 am
I did a small test but wiered results..
User who has dbcreator role created a database "TEST" and I tried this query
EXECUTED as user ='STATELAW\RSquare'
DROP DATABASE
and got this error
Msg 3701, Level 11, State 1, Line 1
Cannot drop the database 'TEST', because it does not exist or you do not have permission.
But wired thing is that user can drop it by running just DROP DATABASE [TEST]
November 5, 2009 at 10:10 am
Tara, one thing I will point out, are you aware that you can grant create database to a login, you dont have to give them the server role dbcreator
SilverFox
If I just give them " Create any Database 'permission under server properties that would pretty much allow the user to create any databases on that server without granting server role "dbcreator"
If that is the case what is the differnce between these 2 options ?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply