Re: Permission to restore databases and access them without being

Hi Lena,
I think that your meaning was that you just wanted to restore the databases copied from your production server and made sure that the creator had privileges to
access the databases, however the creator should not have any privilege to access the existing databases on your test server.
If I am off base, please let me know.

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

Please feel free to let me know if you have any other questions or concerns.

Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@xxxxxxxxxxxxxx
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notifications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================






.

Relevant Pages

  • RE: Server Explorer & Databases
    ... As for the visual studio's database server explorer, ... oracle, visual studio generally use the OLD DB provider which is for Oracle ... and support of most general funcctionalitis in latest version of Oracle. ... #Server Explorer for Oracle Databases ...
    (microsoft.public.vsnet.ide)
  • Re: AS2005 ... what is wrong with it?
    ... What I have seen is that the dev server is *faster* than the prod server ... And does your dev server also have all 6 databases with all these roles? ... When I restore the "PROV" i got approx 20 sec. ... Starting from an empty data folder should show if this theory is true ...
    (microsoft.public.sqlserver.olap)
  • Re: Copying WSS to another Server
    ... the data in sync I back the SQL databases up and restore them to the replica ... It already has WSS installed. ... > on one server and copy it to another for dev/test purposes, ...
    (microsoft.public.sharepoint.windowsservices)
  • Re: AS2005 ... what is wrong with it?
    ... I have 6 OLAP databases on the server. ... When I restore the "PROV" i got approx 20 sec. ...
    (microsoft.public.sqlserver.olap)
  • Re: Exchange DR, what to do
    ... setup blank databases on theDRexchangeserver, ... data out of the RSG, it tries to restore it to the original server, ... the cluster exchange virtual server name was, ...
    (microsoft.public.exchange.admin)