We create multiple databases in sql server 2005. I would like to make sure that every new database that is created has a specific read only account when it gets created. I know there is a way to write code to do this, but is there a way we can set up a database template of some sort so every time a new database is created the account is automatically added from SQL server side rather than the code side?
views:
24answers:
1
+3
A:
If you mean one read-only user account for all databases on a server, you can use this hack:
- Create a new SQL Server login
- Create a new Database user in model database with db_datareader privilegues.
All new databases use the model db as template, so the user will be available in all databases.
But keep the security issues in mind. ;-)
Greets Flo
Florian Reischl
2010-07-21 08:31:17
Any other ways? We have a multi-db environment and a service that needs the read only account added to each so security is not an issue.Joe
JoeP
2010-07-23 04:01:48
What's missing with this approach?
Florian Reischl
2010-07-23 06:45:56
Florian, This approach has not had any luck for us so far. This is the approach we pursued and does not seem to be working as anticipated. We constantly have to run scripts to add the proper users to the db's afterwards. Thanks.
JoeP
2010-07-27 07:02:03