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:
Login 'test1' should be owner of database 'ABC' only and no other database on the server.
Login 'test1' should not have access to any other database on the server.
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?