Re: Permission to restore databases and access them without being

"Charles Wang [MSFT]" (changliw@xxxxxxxxxxxxxxxxxxxx) writes:
You can first grant CREATE DATABASE or assign the fixed server role
dbcreator to the login so that you can restore the databases. You can
find more detailed information in the section "Permission" of "RESTORE
(Transact-SQL)" in SQL Server 2005 Books Online:
http://msdn.microsoft.com/en-us/library/ms186858.aspx

After that, assign your wanted permissions such as db_reader/db_writer
etc to the user on the newly restored databases, but do not assign any
permissions or database roles to the other databases. Of course, if you
want to give thinner granular permission control, you can use GRANT
statements. Please refer to: GRANT Database Permissions (Transact-SQL)
http://msdn.microsoft.com/en-us/library/ms178569.aspx

Charles, the issue is that if the login who performs the RESTORE does not
map to a user in the database, the RESTORE operation fails, even if the
user in the dbcreator role. I know, because I tested this, before I
made my post.


--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: Please answer my queries for fresh Installation
    ... You can restore MSDB as well as master. ... > SQL Server, It will create Master, MSDB databases. ...
    (microsoft.public.sqlserver.server)
  • Re: Moving SQL Server 2000 from NT4.0 to Windows 2003
    ... Detach your existing user databases, ... user databases (or RESTORE) to the new server. ... > Hey Steve, ... Windows 2003 will not allow SQL Server SP less than 3. ...
    (microsoft.public.sqlserver.server)
  • Re: Server Migration...
    ... If you have restored all of your databases, ... Columnist, SQL Server Professional ... Does that mean I need to restore all user databases first and I restore ... Master, MSDB, Model. ...
    (microsoft.public.sqlserver.server)
  • Re: Please answer my queries for fresh Installation
    ... Moving SQL Server Databases ... Using WITH MOVE in a Restore to a New Location with Detach/Attach ... Disaster Recovery Articles for SQL Server ...
    (microsoft.public.sqlserver.server)
  • Re: Configuring SQL 2000 for use with ESRI ArcSDE
    ... You can assign SQL Server users to database roles using sp_addrolemember: ... Permissions are entirely application specific. ... vendor for clarification of security requirements. ... I have Two databases 3 Roles ...
    (microsoft.public.sqlserver.server)