August 3, 2012 at 7:08 am
Hi experts,
My company provides a small hosting environment to a secure customer base, we are very new to providing this type of service and are learning as we go. I am the nominated DBA and are therefore tasked with anything SQL related. We allow different options for the level of hosting and the permissions customer administrator have, for example total managed service where there are no admin privilages through to bespoke admin privilages based on customer requirement and what we are comfortable for them to have e.g not local admin rights.
I have a customer at the moment who has asked to be able to restore databases, now the databases they are restoring exist on our hosting server but are being restored from another dev server outside of our domain/environment. The database has a standard sql account which is named the same on both environments, but when they restore their DB over top of the copy we have in the production environment they run into the orphaned user issue, which they are unable to correct with their current permissions. The admin users also have domain accounts and are part of specific domain groups which I have added to SQL server so that they can log on with windows credentials and perform the restore. I have granted the following to the domain groups: dbcreator and public and have mapped their user accounts to each of their dbs as dbo (as per their request).
However, when the databases are restored the domain groups are also unmapped as part of the restore and becomes inaccessible to the user. So the only way for them to correct this at the moment is for the admins to call my team and for us to correct. I was just wondering what would be the best approach to resolve this, I don't really want to give them any other extra permissions that will give them access over having the ability to correct the issues above, but I am not sure what I can grant them to be able to just do that.
I hope this makes some sort of sense, thank you in advance.
Adam
August 3, 2012 at 9:08 am
while not exactly a hosted environment, we provide our testers with he ability to restore their own specific database from a web page;
the page lets them select the path to the backup, and calls a proc with elevated permissions (so they don't need db_owner/the ability to restore.
you would probably add the fix for the orphaned user issue to the procedure as well..
ALTER USER [Someuser] WITH LOGIN = [Someuser];
the proc also makes sure certain roles and users are added after the restore.
this is a stripped down all TSQL version that might get you pushed in a direction that might work.
in our case, we have 6 "versions" of the same proc(and six web pages) ; one for each testing database they might need to resotre.
USE MASTER;
--the basic setup: we assume we have a user and a user database:
IF NOT EXISTS(SELECT 1 from master.sys.databases where name = 'ClientX')
CREATE DATABASE ClientX;
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'ClientXAdmin' AND type = 'S') --'S' = SQL login
--create our super user
CREATE LOGIN [ClientXAdmin]
WITH PASSWORD=N'NotTheRealPassword',
DEFAULT_DATABASE=[ClientX],
CHECK_EXPIRATION=ON,
CHECK_POLICY=ON
--first we need a sysadmin role with no login, which will be used
--for execution context in the DDL triggers or special elevated permissions functions.
IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N'superman' AND type = 'S') --'S' = SQL login
BEGIN
--create our super user
CREATE LOGIN [superman]
WITH PASSWORD=N'NotTheRealPassword',
DEFAULT_DATABASE=[master],
CHECK_EXPIRATION=ON,
CHECK_POLICY=ON
--make our special user a sysadmin
EXEC master..sp_addsrvrolemember @loginame = N'superman', @rolename = N'sysadmin'
--noone will ever login with this, it's used for EXECUTE AS, so disable the login.
ALTER LOGIN [superman] DISABLE
END
GO
CREATE PROCEDURE sp_RestoreFullFileBackupForClientX(@PathToBackupFile nvarchar(1000))
WITH EXECUTE AS 'superman' --a user in master with the ability to restore databases.
AS
BEGIN --PROC
--get exclusive access to that database right now.
ALTER DATABASE [ClientX] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
--restore the requested database for the specific user.
RESTORE DATABASE [ClientX]
FROM DISK = @PathToBackupFile
WITH FILE = 1,
REPLACE,
NOUNLOAD,
STATS = 10
--after restore make sure specific roles must exist:
IF NOT EXISTS(SELECT 1
FROM ClientX.sys.database_principals
WHERE name = N'AlmostOwners' AND type = 'R')
EXEC('USE ClientX;
CREATE ROLE [AlmostOwners];
EXEC sp_addrolemember N''db_ddladmin'', N''AlmostOwners''
EXEC sp_addrolemember N''db_datareader'', N''AlmostOwners''
EXEC sp_addrolemember N''db_datawriter'', N''AlmostOwners''
--can the users EXECUTE procedures? comment out if false
GRANT EXECUTE TO [AlmostOwners]
--allow the users to see view proc and function definitions
Grant View Definition ON SCHEMA::[dbo] To [AlmostOwners]
');
IF NOT EXISTS(SELECT 1
FROM ClientX.sys.database_principals
WHERE name = N'ClientXAdmin' AND type = 'S')
EXEC('USE ClientX;
CREATE USER [ClientXAdmin] FOR LOGIN [ClientXAdmin];
EXEC sp_addrolemember N''AlmostOwners'', N''ClientXAdmin''
');
--just in case, set it to multi_user.
ALTER DATABASE [ClientX] SET MULTI_USER;
END --PROC
GO
--create a user in master for ClietnX
CREATE USER [ClientXAdmin] for LOGIN [ClientXAdmin];
GRANT EXECUTE ON sp_RestoreFullFileBackupForClientX TO ClientXAdmin;
GO
this is the code i just used to test:
--the test harness
EXECUTE AS LOGIN = 'ClientXAdmin'
select suser_name();
--I'm ClientXAdmin!
--i KNOW this is a path to the database.
declare @path nvarchar(1000) = N'C:\data\backups\PERFECT1100_01052012.BAK'
exec sp_RestoreFullFileBackupForClientX @path
--change back into superman
REVERT;
--cleanup
/*
USE master;
drop database ClientX;
drop user ClientXAdmin;
drop login ClientXAdmin;
drop procedure sp_RestoreFullFileBackupForClientX;
*/
Lowell
August 3, 2012 at 1:19 pm
was this even close remotely what you were looking for?
Lowell
August 3, 2012 at 2:03 pm
Hi Lowell, apologies for the delayed response... Been tied up with DPM backup issues!! Yes that's great just what I was looking for, I will give it a go on Monday. I didn't know if it was a daft question as I would have thought SQL server would have had more granularity, although from what I have seen of 2012 it may address this type of thing now.... I hope. Thanks again, very much appreciated.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply