SQLTeam.com | Weblogs | Forums

Permissions question on copied database

sql2008r2

#1

Hope someone can help.

We have a financials database that sits on server A. Every night at 2am, this database is backed up and a copy is placed on server B.

Access to the database on server A is strictly controlled. However, server B is a test server and we are free to add users as we see fit - if someone does something stupid then hard luck, they'll have to wait for the next 2am copy!

Is there a way to automatically add users at the end of the SQL job that does the copy? At the moment, a user named testreporter (who isn't allowed access to server A) has to be added manually every morning (right-click on the database / properties / permissions / search for user / check "select" and "connect" permissions.

Thanks in advance.


#2

just add CREATE USER comand at the end of the restore script


#3

As a bit of a sidebar...

Virtually anything you can do with the GUI can be done in code. If you don't already have it, download "Books Online", which is the local help system for SQL Server and start reading.