February 26, 2010 at 8:13 am
Is it possible in Policy management or anywhere else in fact, to restrict where developers create there databases. I want to give them the facility to create databases but only in the location set by myself.
February 26, 2010 at 8:28 am
MarvinTheAndriod (2/26/2010)
Is it possible in Policy management or anywhere else in fact, to restrict where developers create there databases. I want to give them the facility to create databases but only in the location set by myself.
Now not having messed much yet with Policy Management I would expect there should be a way to do that as a condition. When CREATE DATABASE command is given if you can check the file path and if it does not match what you want, then deny the transaction?
There is, under the Database facet, a property value @PrimaryFilePath [The path and name of the operating system directory that contains the primary file for the database] that can probably work to your advantage. Please post it if you figure it out I would be very interested in hearing how you got it to work.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
February 26, 2010 at 8:34 am
I'm not entirely clear on what you mean.
Do you mean you want to restrict the location of the files (mdf and ldf and ndf)? Or the server(s) they can create databases on? Or something else?
If it's files, then that's a question of restrictions in Active Directory to control what folders and drives they can create files in.
If it's servers for databases, that's SQL security to allow/block database creation on the right servers.
Totally different handlings, and I'm not sure which is correct here.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 26, 2010 at 8:52 am
GSquared (2/26/2010)
If it's servers for databases, that's SQL security to allow/block database creation on the right servers.
I want them to be able to Create databases, but for the locations that they can create database files to be restricted. ie. Data files can only be created in E:\SQLDATA\ log files in F:\LOGFILES\ etc
February 26, 2010 at 9:02 am
I have found a way to do this through Melton's suggestion. However this is my first experience with policy management. I have evaluated the policy and it confirms that existing databases conform to this policy.
However When i go to change the evaluation mode SQL offers
:
* On Demand
* On Schedule
According to blogs I have read it should also offer
* On Change - Log Only
* On Change - Prevent
These options are not present what am i doing wrong ?
February 26, 2010 at 9:31 am
It is probably not allowed mode with the facet, according to TechNet
Allowed Evaluation Modes
The policy evaluation modes are determined by the characteristics of the Policy-Based Management facet that is used by the policy. All facets support On demand and On schedule. Facets support On change: log only if the change of the facet state can be captured by some events. Facets support On change: prevent if there is transactional support for the DDL statements that change the facet state. Policies that are automated with one of these three execution modes can be enabled and disabled.
In SQL Server Management Studio, the Evaluate Policies dialog box provides two options that you can use to run a policy:
Evaluate
This will evaluate policies against the selected targets.
Apply
This enables you to apply changes to applicable targets that violate policies. Some targets are not reconfigurable through Policy-Based Management. For example, if you are evaluating whether backup and data files exist on separate devices, Policy-Based Management can detect the violations to this condition; however, you cannot apply changes through Policy-Based Management to enforce policy compliance.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
February 26, 2010 at 9:31 am
Create a procedure that creates the db for them, they just have to pass in db name.
you can specify the location and any database file specs you want them to follow.
you can make this sproc as fancy as you want; like have it send an e-mail to you or the whole company:-) when it's been run ...
This way you don't have to elevate any of the developer permissions.
Enjoy...
"give them the tools :smooooth:... not the keys:satisfied:".
Enjoy
"Give them the tools:crazy: ... Not the keys:smooooth:"
February 26, 2010 at 9:36 am
SQLDraggon's suggestion sounds good, and more controllable.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
February 26, 2010 at 9:39 am
Melton (2/26/2010)
SQLDraggon's suggestion sounds good, and more controllable.
... but only if the only way they can create a new database is by using this procedure. You have to revoke their right to execute the Create Database command.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
February 26, 2010 at 9:46 am
Alvin Ramard (2/26/2010)
Melton (2/26/2010)
SQLDraggon's suggestion sounds good, and more controllable.... but only if the only way they can create a new database is by using this procedure. You have to revoke their right to execute the Create Database command.
This is not an option at my company (politics)
February 26, 2010 at 9:48 am
MarvinTheAndriod (2/26/2010)
Alvin Ramard (2/26/2010)
Melton (2/26/2010)
SQLDraggon's suggestion sounds good, and more controllable.... but only if the only way they can create a new database is by using this procedure. You have to revoke their right to execute the Create Database command.
This is not an option at my company (politics)
Then, don't forget to say "Please" when you ask them to use the stored procedure.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
February 26, 2010 at 10:20 am
MarvinTheAndriod (2/26/2010)
This is not an option at my company (politics)
Is what you wanting want to do with the SQL Policy in there as a company policy or standard?
If it is, then you can just set your Policy to run as a schedule. When you find one that violated it, politely go up to their desk smack them in the back of the head, and tell them to put it where you were told to put it.:hehe:
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply