EXECUTE AS question

Question

EXECUTE AS question

chuck.forbes

SSCarpal Tunnel

Points: 4294
More actions
April 15, 2016 at 4:51 pm

#306634

We have an application which requires it's database to be restored to a pre-production state, as designed by the vendor. I'd like to hand this ability off to an operator (as they're already the people calling the vendor when issues arise in the software).
I don't want to give these operators db_creator, nor do I want to make their account the database owner, so I'm thinking that using EXECUTE AS in a procedure with a RESTORE statement would be a workable solution. How does that actually work, though?
I guess where I'm getting confused is that, I'm putting this procedure in a database, but it needs DDL rights outside of the database context. I'm assuming that I can't just put EXECUTE AS LOGIN = 'sa' (using sa only as an example) in any procedure and run it successfully, as that would be a huge security hole in SQL Server. So is there an added permission I'm supposed to provide in this case?
--=Chuck

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 100950 More actions · Answer 1

Yeah, definitely not 'sa' or some other existing/standard user.

Create a new user, native SQL user will do if that's available to you, and grant it just the specific permissions it needs to do the restore, etc.. For example, make that new user a 'dbo' user in the db to be restored (but don't make it the actual owner/db_owner of the db).

Then have the proc EXECUTE AS that user. Grant the operators' role the right to execute the proc.

It usually takes some time to iron out permissions issues, but I think you should be able to tweak that general approach to get to where you need to be.

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

chuck.forbes SSCarpal Tunnel Points: 4294 More actions · Answer 2

Are you saying that I should put this restore-capable user account into the db_owner role for the database? I haven't heard of making someone a dbo user (I always took that to mean they were the actual owner of the database).

--=cf

ScottPletcher SSC Guru Points: 100950 More actions · Answer 3

chuck.forbes (4/18/2016)
Are you saying that I should put this restore-capable user account into the db_owner role for the database? I haven't heard of making someone a dbo user (I always took that to mean they were the actual owner of the database).
--=cf

In order to allow that user to restore the db, I don't think you have a choice.

This code will give someone full db ownership authority without making them the single 'dbo' of the db.

USE db_to_allow_restores_of

EXEC sp_addrolemember 'db_owner', 'special_user_name'

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

chuck.forbes SSCarpal Tunnel Points: 4294 More actions · Answer 4

It looks like the user account actually has to be the actual owner of the database to perform the restore. Being a member of db_owner isn't privileged enough. Aside from that, the other option is that the user can be in the server-level dbcreator role (or sysadmin, if you want to go there).

So, I created a procedure in a throwaway database:

CREATE PROCEDURE [throw_away_db].[dbo].[test]

-- Add the parameters for the stored procedure here

AS

BEGIN

--user is in dbcreator

execute as login = 'can_restore';

RESTORE DATABASE [attempt_restore]

FROM DISK = N'E:\MSSQL\Backup\attempt_restore_201604181219.bak'

WITH

FILE = 1, NOUNLOAD, STATS = 5;

END

It executes using my sysadmin account. But with any other account I try & create, I get the following:

Cannot execute as the server principal because the principal "can_restore" does not exist, this type of principal cannot be impersonated, or you do not have permission.

I tried granting the grant IMPERSONATE privilege, and was still stymied.

I took another tack, and I think I can get the same thing done in SSIS. There, I'm allowing a user to launch the *.dtsx, but the username and password for the database are actually being submitted by the program at runtime. So I get the same effect of the executing user not having the privileges to perform the RESTORE process. I ultimately was going to execute the sp from SSIS, anyways (since I needed to move some non-db files around as part of the restore).

Sachin Nandanwar SSCertifiable Points: 7699 More actions · Answer 5

If the database does not exists then to restore the database the user must have create database permission but if it exists then user must be a member of dbcreator,the db owner(dbo) and sysadmin.db_owner role do not have the restore permission.

--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server

chuck.forbes SSCarpal Tunnel Points: 4294 More actions · Answer 6

Sachin, that's what I understand. My question is how can I setup a procedure that restores an existing database, that someone who is not in these roles can execute. I am assuming that I'd be using EXECUTE AS login = 'some-privileged-user', but since dbcreator and sysadmin are server level permissions, I'm unsure that there's a way to set such a procedure up within a database. If that was allowed, then anyone with 'CREATE PROCEDURE' privileges could circumvent basic security on the server, but simply adding something like the following to the top of a procedure, EXECUTE AS login = 'sa'.

--=Chuck