October 2, 2009 at 12:50 am
Hello again,
I'm a (still) starter as DBA,
I got a task, to make a "sand box database" for developers in some server.
they want db_owner role, its OK. But I want to give them fixed disk space for data files.
Is there any way to deny ONLY altering DB file properties, like size, autogrouth the number of files.. etc?
October 2, 2009 at 3:55 am
moderis (10/2/2009)
Hello again,I'm a (still) starter as DBA,
I got a task, to make a "sand box database" for developers in some server.
they want db_owner role, its OK. But I want to give them fixed disk space for data files.
Is there any way to deny ONLY altering DB file properties, like size, autogrouth the number of files.. etc?
Most of the properties are held in the master database, so certain properties and actions they will not be able to do, even with the DB_owner right.
The easiest way of showing this for you, is for you create a sql login give it db_owner rights in the database and connect to the server again using that sql login and try and alter the database properties. It will give you piece of mind, doing it this way.
--------------------------------------------------------------------------------------
[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]
October 2, 2009 at 6:49 am
I'd try to create server level DDL trigger and hook it up to ALTER DATABASE event. Then depending on the EVENTDATA and login executing the statement you might decide to rollback the action.
Have a look if it works for you.
Regards
Piotr
...and your only reply is slàinte mhath
October 2, 2009 at 7:27 am
It is a problem to put any limitations on the db_owner role.
Yes, db_owner cannot see properties of the own database (like file size) through GUI, but he can easily run a script like USE master
GO
ALTER DATABASE Test
MODIFY FILE
(NAME = Test,
SIZE = 22MB)
GO
and change datafile size. This script use a system SP sp_dboption behind a scene, and I don't know a way how to restrict db_owner permission to execute it.
The idea of server level DDL trigger sounds good.
Another way to restrict physical disk space for developer databases is just put their databases on a separate mount point.
October 2, 2009 at 7:36 am
another way of doing it is to :
DENY ALTER ANY DATABASE to [login]
make sure the master database is selected before running
--------------------------------------------------------------------------------------
[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]
October 2, 2009 at 7:43 am
Silverfox (10/2/2009)
another way of doing it is to :DENY ALTER ANY DATABASE to [login]
make sure the master database is selected before running
In this case the developer cannot create/modify any database opject in his own database.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply