Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Server 2008 - Hosting permissions


SQL Server 2008 - Hosting permissions

Author
Message
acrutchley
acrutchley
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 349
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
Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14984 Visits: 39014
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell
Lowell
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14984 Visits: 39014
was this even close remotely what you were looking for?

Lowell

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

acrutchley
acrutchley
Valued Member
Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)Valued Member (53 reputation)

Group: General Forum Members
Points: 53 Visits: 349
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search