October 10, 2012 at 2:09 am
I have a team that need to restore databases provided by clients, but I cannot give them sysadmin permissions.
At the moment they can restore the database and appear as the DB owner when looking at the DB properties. However, they do not appear under security -> logins. This means that they cannot access the restored database and cannot grant access permissions to others, even thought they are the DB owner.
Members of this team have been allocated to Roles bulkadmin, dbcreator, processadmin, securityadmin and serveradmin.
Any ideas how I can overcome this issue, without a sysadmin having to add their logins as users? Thank you in advance.
Colin
October 10, 2012 at 5:16 am
How about writing a script to add their logins to the database? They should then be able to run the script after the restore. It would look something like
-- Add User to database
USE YourDatabaseHere;
CREATE USER TeamMember FOR LOGIN [YourDomainName\TeamMember ];
EXEC sp_addrolemember 'db_datareader', 'TeamMember'
EXEC sp_addrolemember 'db_datawriter', 'TeamMember'
Use whatever role is appropriate.
October 10, 2012 at 6:03 am
I though that would work as well. However, as they do not appear under Users, the code gets rejected. I am now working on a script that the team can update and which will be run by a job that is owned by myself. The job is to run on a regular basis and will (hopefully) update the permisions as it is being run by a sysadmin.
Only testing will tell.....
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply