Re: Permission to restore databases and access them without being sysa

LenaMsdn08 (LenaMsdn08@xxxxxxxxxxxxxxxx) writes:
How can I give a user permissions on SQL Server 2000 so they can restore
a database (copied from the Production server) to the Test server, and
get access to the database they just restored? I assigned them to the
dbcreator server role and that lets them restore the database, but not
access it.

The server is a test server, but there are several databases on it with
confidential content, so at the same time I need to make sure they cannot
gain access to those databases. For that reason, I can't put them in the
sysadmin server role.

I think they answer is that they can't, and they shouldn't. To wit, they
should not be able restore the backup of any those confidential databases,
and get access to them that way. So if they are not already a user of the
database they are restoring, RESTORE should fail.

This is a little difficult for me to test, but maybe it works if they
access the test box with their Windows login, and this Windows login is
already a user in the production database. But reasonably, they should only
be able to access the database with the same permission as they had one
the production machine.

A variation is that they bring over the backup to their local machine
where have sysadmin rights, restore the database, change the database
owner take a new dump and then move it to test.

Then again, this open the question whether they can freely copy files
around. A bit dubious given those confidential databases.


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

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

.

Relevant Pages

  • RE: Backups have Shadow Copy Problems
    ... and restarted the server. ... suggested and changed the recovery model to simple on the one database called ... I understand the issue to be: the backup task failed ... You back up data from a volume that contains a Microsoft SQL Server ...
    (microsoft.public.windows.server.sbs)
  • Re: upsizing to sql 2005
    ... the word SERVER in it, ... You can access to the database by multiple means (Access, ... and how does it update the SQL database with the new records in Access? ... Query Name: Arcadia - ARC ...
    (microsoft.public.access.queries)
  • Re: Linked Tables in Access
    ... any use of SQL Passthru, Linked Tables or any other use of MDB / MDE ... server, or would I also need to convert *those* queries to passthrough ... I've been trying to understand why Access database files become corrupt. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Trouble Getting VS.Net 2003 WalkThroughs MSDE Connection
    ... Config Tool of SQL Server? ... > link to download the PUBs database. ... >>> Setup and they directed me to install MSDE and they attached a ...
    (microsoft.public.sqlserver.msde)
  • Re: Leveling by ID vs. "Standard"
    ... of this trade called Project Management. ... a database for the "Project Tables," ... to write reports on data from the server database. ... product supporting queries created with SQL. ...
    (microsoft.public.project)