Restore without sysadmin permisions

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Restore without sysadmin permisions

Restore without sysadmin permisions

Rate Topic

Display Mode

Topic Options

Author

Message

Colin Betteley

Posted Wednesday, October 10, 2012 2:09 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 22, 2013 10:14 AM
Points: 169, Visits: 314

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

Post #1370752

PurpleLady

Posted Wednesday, October 10, 2012 5:16 AM

Hall of Fame

Group: General Forum Members
Last Login: Monday, May 13, 2013 5:48 AM
Points: 3,577, Visits: 541

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.

Post #1370858

Colin Betteley

Posted Wednesday, October 10, 2012 6:03 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, March 22, 2013 10:14 AM
Points: 169, Visits: 314

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.....

Post #1370868

« Prev Topic | Next Topic »

Permissions