Permission to restore databases and access them without being sysa   

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.

date: Sun, 10 Aug 2008 10:55:01 -0700   author:   LenaMsdn08 am

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

LenaMsdn08 (LenaMsdn08@newsgroup.nospam) 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@sommarskog.se

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

date: Sun, 10 Aug 2008 15:05:03 -0700   author:   Erland Sommarskog

Re: Permission to restore databases and access them without being   

You're probably right. The databases they will be working with aren't 
sensitive, but other databases on the test server are. Better safe than 
sorry, I can just restore the databases for them as needed and assign access.

Thanks again for the help!

"Erland Sommarskog" wrote:

> 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@sommarskog.se
> 
> 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
> 
>

date: Sun, 10 Aug 2008 16:01:02 -0700   author:   LenaMsdn08 am

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@microsoft.com.
===========================================================
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.
=========================================================

date: Mon, 11 Aug 2008 05:23:24 GMT   author:   (Charles Wang [MSFT])

Re: Permission to restore databases and access them without being   

"Charles Wang [MSFT]" (changliw@online.microsoft.com) 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@sommarskog.se

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

date: Mon, 11 Aug 2008 00:45:44 -0700   author:   Erland Sommarskog

Re: Permission to restore databases and access them without being   

Hi Erland,
Thank you for your clarification. I misunderstood before, but anyway I had some 
interesting findings here based on my test and I would like to post it here.

Yes, if the login was not existed in the old database, the RESTORE statement was 
executed with the error message:
Server user '%s' is not a valid user in the database '%s'.
(Known issue in the KB article, http://support.microsoft.com/kb/240872/en-us.)

The RESTORE seemed failed, right? However it was not true. The database was actually 
restored even with this error message. The problem was that the current user could not 
be created in the newly restored database. However you can manually assign the 
permissions with a sysadmin account. There were no data loss based on my observation.

You may further check this. I think that we can make a conclusion that even the user is 
not existed in the original database at that time, the database can be restored, however 
you need to manually resolve the permission issues after you restore the database. 

Thank you again for your great contributions to Microsoft Managed Newsgroup and Have 
a nice day!

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@microsoft.com.
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

date: Mon, 11 Aug 2008 09:11:23 GMT   author:   (Charles Wang [MSFT])

Re: Permission to restore databases and access them without being   

"Charles Wang [MSFT]" (changliw@online.microsoft.com) writes:
> The RESTORE seemed failed, right? However it was not true. The database
> was actually restored even with this error message. The problem was that
> the current user could not be created in the newly restored database.
> However you can manually assign the permissions with a sysadmin account.
> There were no data loss based on my observation. 

Later I decided that I could have use for that database on that server,
so I restored it as sa. And got no error message that it was already
there.

I should add that I ran this test on SQL 2000, as that was the server
version Lena asked about.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

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

date: Mon, 11 Aug 2008 14:26:35 -0700   author:   Erland Sommarskog

Re: Permission to restore databases and access them without being   

Hi Erland,
You could not reproduce the error if you use 'sa' which is a built-in account. You can simply 
create a SQL login and only grant CREATE DATABASE permission to it. Then log on your SQL 
Server with the new login and run the RESTORE DATABASE statement. Then you should be 
able to reproduce the error message and if you open Enterprise Manager or refresh the 
database list in Query Analyzer, you will find that the database is created.

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@microsoft.com.
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

date: Tue, 12 Aug 2008 09:57:47 GMT   author:   (Charles Wang [MSFT])