Creating a SQL Server 2005 User Login to Backup and Restore on a Single Database

Question

I have a SQL Server 2005 database server with multiple databases on the server. I need to create a user login 'test1'with the following restrictions:

1. Login 'test1' should be owner of database 'ABC' only and no other database on the server.

2. Login 'test1' should not have access to any other database on the server.

3. Login 'test1' should be able to backup (data and log files) and restore the database 'ABC' only. Login 'test1' should not be permitted to backup or restore any other database on the server.

I created a login named test1 and opened the properties window. Under Server roles, public is checked and under User Mapping, the database ABC is checked and the default schema is db_owner. Using a t-sql script, I successfully backed up and restored the ABC database. When I attempted to restore another database using another backed up database, I was successful even though I could not access the other database.

Any ideas on how to resolve this issue?

How do I create and set the correct permissions for this user login? Will I need to create a custom t-sql script or can it be done using the GUI interface?

Answer 1

Logins are used on the Server level and Users are used on the Database Level. You can read about it here https://stackoverflow.com/questions/5247404/sql-server-server-login-vs-database-login

You can do it in Management Studio or with t-sql. In Object Explorer go Security->Logins Right click->New login. After you have created login test1 right click on database ABC -> Properties -> Files page, select owner test1.

Login test1 will not have access to other databases until you create a user for it in these databases.