We have SQL server 2008 R2 and 12 databases under a single instance. I am trying to grant a user permission so that he :
1. Has full access to 3 of the existing databases including restoring and overwriting them
2. Is able to create new databases and have full access to them
3. Does not have unnecessary rights to other existing databases
What is the best way to accomplish this?
I understand that the dbcreator server role will grant the permission to create databases but it also allows the user to ALTER/DELETE all databases. Because of the third point above, I opted not to use this dbcreator role. Instead I used GRANT CREATE ANY DATABASE command so that he can create databases and only ALTER/DELETE the databases that he owns.
The user is able to create a new database, he is also create a new database by restoring from a backup file. However, he is not able to access them. He tries to access them via SSMS and he sees the error "The server principal loginname is not able to access the database DB under the current security context. Error 916."
I would appreciate any input.