October 16, 2008 at 10:04 am
On our development 2005 box, we're going to give developers several DB roles to all databases. Datareader, datawriter, ddladmin, securityadmin. I was thinking we could add these rights to the model db, and then in the future, they'll automatically get these roles on new db creation.
This works, however, it struck me that it may have issues. This will also give them the ability to modify things in the model database, which isn't desired. Is this correct? I don't suppose there's a way to setup a permissions model, without actually giving them those permissions to the model db?
Another idea I had dealt with files. A best practices thing I've read recommeneded 0.5 - 1 data files per CPU core. As we have 8 cores on the server, I was going to make 4 datafiles per new database. Thinking again of the model DB, I attempted to set it up there. It turns out you can't modify the files of model. I suppose scripting is my answer here?
I'm guessing I'll have to go the scripting route with both of these issues, which is kind of a bummer since the model DB sounds like a useful way to do these sorts of things.
As far as scripting goes, I don't suppose there is a way to automatically run a script on new DB creation?
October 17, 2008 at 8:35 am
:blink: huh?
1- Breath
2- Rework your post
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 17, 2008 at 1:25 pm
1. You are correct. If you grant people into model, they will have access in model as well as being granted into any newly created db. There isn't a way around this that doesn't involve "ungranting" some level of access to model during the period when you're not creating a database
2. What you need is to create the database with a script to add new files in "automatically" This script can also do things like add logins as users and users into roles (after the db is created).
3. you can automatically do stuff after the creation of a database with ddl triggers in SQL 2005. I don't know if adding users will work in DDL Trigs because of some rules about transactions inside ddl triggers.... maybe it will.
What I think you really need is a dynamic SQL statement that takes the name of the new database as a variable and generates the syntax to create the new files, users, etc. Then runs those statements.
Sounds like fun, good luck!
Craig Outcalt
October 20, 2008 at 11:58 am
Thanks SQLBot, scripting it is. I was hoping to dumb down the process so anyone can do it all through SSMS' GUI, but sounds like that's not really in the cards.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply