Granting a Developer Restore Permissions
Kalen Delaney's article "All About Restore" (May 2002, InstantDoc ID 24340) was very helpful, and I'd like to know more about SQL Server permissions. Specifically, I wish DBAs could grant Restore permissions to a developer in a test or development environment without having to give the developer sa rights.
You can give a developer permission to create a database if you add her as a user to the master database. With permission to create a database comes permission to restore a database. However, even though the developer might have restored the database, she'll have only the permissions that she had when the database was originally backed up. In fact, all permissions in the database will be as they were originally; hopefully, this is what you want.
If you want the developer to be the owner of the database after the restore, she needs to be the owner before the backup is made. Alternatively, after the restore, a sysadmin can change the owner of the database to this developer.
Note that a small bug is connected to having someone who isn't the owner of the database perform the restore. When you execute sp_helpdb, the report will say that this developer is the owner, but inside the database, the original owner is still dbo and the developer who restored the database will have only the permissions she had originally, which might be no permissions at all. This shouldn't change the behavior of any application using this database, because during runtime, only the permissions within the database are checked.
If you would like to see other behavior, you can write to [email protected] Members of the SQL Server development team read all suggestions they receive.
Sold on Filegroups
Kimberly L. Tripp's article "Before Disaster Strikes" (September 2002, InstantDoc ID 25915) was one of the best and most detailed articles about backup strategies that I've ever read. The benefits of using filegroups impressed me so much that I'm trying to make a case for using them in the company I work for. Thanks for the tremendous insight.
Guide to Those Flying by Seat of Their Pants
I work for a large book-printing facility in the Midwest that decided to move to SQL Server about a year and a half ago. I was chosen to learn SQL Server, administer our databases, and lead in the design and implementation of SQL Server solutions. I have an RPG background, so I was excited about learning something new and, at the same time, overwhelmed. I spent many days and nights reading books from Microsoft. Then I found the SQL Server Magazine Web site and have been an avid visitor for the past year. I also subscribed to the magazine and have based a number of our strategies and solutions on techniques and solutions that I've read in its pages.
For people like me, who have to fly by the seat of their pants at times, your magazine is a lifesaver. The Web site forums are fabulous, and I'm on them almost every day seeing if other people are in the same quandaries that I'm in. Keep up the good work; you make my job a lot easier.