Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Server 2008 - Hosting permissions Expand / Collapse
Author
Message
Posted Friday, August 03, 2012 7:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 04, 2014 5:55 AM
Points: 12, Visits: 128
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
Post #1339800
Posted Friday, August 03, 2012 9:08 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:01 PM
Points: 12,744, Visits: 31,074
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1339910
Posted Friday, August 03, 2012 1:19 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:01 PM
Points: 12,744, Visits: 31,074
was this even close remotely what you were looking for?

Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1340064
Posted Friday, August 03, 2012 2:02 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 04, 2014 5:55 AM
Points: 12, Visits: 128
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.
Post #1340099
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse