Creating new users
-
Hi everyone.
After some advice on how I can ensure that the same users are created each time I create a new database.
Is this something I can set up within the Model database ?
What I want to acheive is when I create a new database , as I have to do each month , can I configure something to automatically create the same users \ access permissions ?
I could do this manually , but I was wondering what the best way to do it was to ensure it was always done correctly ? Other than me manually doing it !! ( This is where mistakes come in......)
Cheers
-
I don't know if setting it up in the Model db will work (maybe someone else will tell you if it will but meanwhile you could always test it by creating a test db and seeing if it works)...
The ideal thing to do would be to create a database role and assign it with all required permissions - then add the list of users to this role and use sp_addrole...
**ASCII stupid question, get a stupid ANSI !!!** -
The short answer to your question is yes and that's really what model is there for. All objects created in model including tables, users, sp's etc will be propogated to any new databases created on that instance or server. You can also do other things such as specify a default size for new db's in model if desired.
My hovercraft is full of eels.
-
Model will work but setting permissions automatically strikes me as dangerous. Why not write a script which at least you have to conciously run each time?
-
Sounds like a better idea
. -
Guys
Thanks for all the replies. Certainly given me something to think about.
appreciate the advice.
-
BTW what's a model?
-
The Model database that is created automatically when you install SQL Server. Whenever you create a new database, it is created as a copy of the Model database including tables, views, triggers, stored procedures, users, etc. - and configuration settings such as collation sequence.
-
It's the model database. This is the database that is copied to create a new database (all objects, defaults and user created are copied).
-
A template (if you will) that you can use to modify and configure specific db requirements so all databases created are "modeled" on the model db....
**ASCII stupid question, get a stupid ANSI !!!** -
Is it echo in here???
-
It may be easier to think of model as a template for other DB's that are created. All objects that exist in model are used as a kind of default settings for any newly created databases on that instance or server. If you have certain triggers, stored procedures, or other things you want to have in eacn and every database that you create you only need to build them once in model. Then every new database created will inherit these items. In addition, if you always want to have new databases created with a minimum data file size of say 50 megabytes, you can specify this setting in model as well.
As previsously stated by Remi and others, this may not always be a good idea for users but it can be done.
My hovercraft is full of eels.
-
Oops...didn't see y'all's posts. But TMI (too much info) is sometimes better than none, no?
My hovercraft is full of eels.
-
y'all's posts...I axe you....
btw...I noticed today that you've made a great impression on sufian...way to win people over!
**ASCII stupid question, get a stupid ANSI !!!** -
Yup it is
.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply