SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Preventing from "create database" with data and log file on C: drive.


Preventing from "create database" with data and log file on C: drive.

Author
Message
Siva Ramasamy
Siva Ramasamy
Say Hey Kid
Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)

Group: General Forum Members
Points: 711 Visits: 342
Hello everyone,

Thanks for looking in to my question.

I have a SQL Server 2008R2 dev environment. I have set the default data and log files path to some other drives other than C: drive.

But when users create databases on the instance, they create it on the C: drive, which is causing the C: drive to run out of space.

Any idea how can I prevent this..?

I tried policy based management, but "on change prevent" is not available for datafile.

Help please...

Thanks!
Siva.
Jason Selburg
Jason Selburg
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

Group: General Forum Members
Points: 6930 Visits: 4115
Easiest way. Don't give them permissions to create databases.

**I know that sounds like a smart-a$$ answer, but it's the best answer IMHO.

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
Siva Ramasamy
Siva Ramasamy
Say Hey Kid
Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)

Group: General Forum Members
Points: 711 Visits: 342
Jason,

Thanks for your response.

I tried many times and have failed to convince the management...:-(

Thanks!
Evil Kraig F
Evil Kraig F
SSC-Insane
SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)SSC-Insane (20K reputation)

Group: General Forum Members
Points: 20611 Visits: 7660
Siva Ramasamy (9/25/2012)

But when users create databases on the instance, they create it on the C: drive, which is causing the C: drive to run out of space.

Any idea how can I prevent this..?


Look into server event triggers. I believe you might be able to capture the attempt and disrupt it if they're going to C:, but I'm not sure and haven't done a lot of research there.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Siva Ramasamy
Siva Ramasamy
Say Hey Kid
Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)Say Hey Kid (711 reputation)

Group: General Forum Members
Points: 711 Visits: 342
Thanks Craig..Let me try that.
Lowell
Lowell
SSC Guru
SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)SSC Guru (71K reputation)

Group: General Forum Members
Points: 71794 Visits: 40942
i think in addition to a DDL trigger, you want to change the default paths as well;
a simple CREATE DATABASE SANDBOX' command doesn't specify the path, so unless you changed it, it's going to get created in the default directory, probably something like C:\Programfiles\Microsoft SQL Server\MSSQL.1\MSSQL or something.

if you right click on a server in SSMS and select "facets", you can change the default paths for backups and the default directory for 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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search