Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Sep 9 '11 at 14:14

This question came from our site for professional and enthusiast programmers.

add comment

1 Answer 1

Logins are used on the Server level and Users are used on the Database Level. You can read about it here 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.

share|improve this answer
    
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? –  Stephen Sep 9 '11 at 14:05
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.