April 15, 2016 at 4:51 pm
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
April 18, 2016 at 10:14 am
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 18, 2016 at 11:55 am
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
April 18, 2016 at 12:53 pm
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 18, 2016 at 2:25 pm
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).
April 18, 2016 at 10:01 pm
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
April 19, 2016 at 8:35 am
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply