September 4, 2014 at 9:27 am
Hello,
* SQL 2008r2/S.E
I have a database being mirrored successfully on different boxes, no witness.
The goal is in an emergency situation, to have a generic oncall user (non DBA) be able to break mirroring (switch partners) and make the mirrored database available. The code would be called from a batch file (not SSMS gui). Assume we lost the Primary database.
As DBA, I can run the SPs successfully. As a generic user account (ie: LowPrivilegeUser), it consistently fails at the first step/stored procedure (pls see below). The generic account will work if I grant it DBA rights (sysadmin), which I would like to avoid. Also, db_owner in master db for generic account fails as well.
Any thoughts on minimum permissions or work around for non DBA?
Many thanks.
Jeff
-- code
-- note: SPs and security created it advance by DBA
-- generic acct would only need to call/exec SPs
use master;
go
-- create SP 1:
IF OBJECT_ID ('DR_p_01_SwitchDBPartner') IS NOT NULL
DROP PROC DR_p_01_SwitchDBPartner
go
CREATE PROCEDURE DR_p_01_SwitchDBPartner
AS
SET NOCOUNT ON
ALTER DATABASE DBMirror SET PARTNER OFF;
go
-- create SP 2:
IF OBJECT_ID ('DR_p_02_EnableDBPartner') IS NOT NULL
DROP PROC DR_p_02_EnableDBPartner
go
CREATE PROCEDURE DR_p_02_EnableDBPartner
AS
SET NOCOUNT ON
RESTORE DATABASE DBMirror
WITH RECOVERY;
-- security
GRANT EXECUTE ON DR_p_01_SwitchDBPartner TO [LowPrivilegeUser];
go
GRANT EXECUTE ON DR_p_02_EnableDBPartnerTO [LowPrivilegeUser];
go
-- to be included in batch file
-- this step run as acct "LowPrivilegeUser"
exec DR_p_01_SwitchDBPartner; -- FAILS HERE
go
exec DR_p_02_EnableDBPartner; -- NOT RUN
-- error (as "LowPrivilegeUser")
Msg 927, Level 14, State 7, Procedure DR_p_01_SwitchDBPartner, Line 8
Database 'DBMirror' cannot be opened. It is in the middle of a restore.
Many thanks. Jeff
September 4, 2014 at 10:03 am
The lower privilege user will need permissions to alter the database (mirror and principal). Or they will need to be a member of sysadmin.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 4, 2014 at 8:31 pm
If you grant exec permission to that user for that SP, wouldn't that work?
Regards,
SQLisAwe5oMe.
September 4, 2014 at 10:11 pm
SQLisAwE5OmE (9/4/2014)
If you grant exec permission to that user for that SP, wouldn't that work?
By default, sprocs will execute using the permissions of the caller. So, not that won't work UNLESS the sprocs include "EXECUTE AS OWNER".
September 10, 2014 at 10:46 am
Hello,
thanks for the responses.
BOL states database owner (dbo) should work from either partner. This only worked from the Principal partner for me. The acct had to be sysadmin when breaking mirroring session from secondary partner?
Any thoughts?
BOL: http://msdn.microsoft.com/en-us/library/ms180801(v=sql.105).aspx
Testing ...
-- Goal: run following from secondary server/Mirrored db as non DBA acct
-- stop DB mirroring
use master;
ALTER DATABASE xxx
SET PARTNER OFF;
go
-- error message for tests
Msg 927, Level 14, State 7, Line 1
Database 'xxx' cannot be opened. It is in the middle of a restore.
-- non sysadmin acct permission tests:
1. ALTER on PRIMARY db >> error
2. Set db_owner on PRIMARY db >> error
3. set db_owner on master db (on Principal/Secondary srv) >> error
4. ALTER ANY DATABASE (on Principal/Secondary srv) >> error
5. DBO on PRIMARY db >> break mirroring worked from Principal server >> error running from Secondary server perspective
6. Restored mirrored db as the low account (dbo in db properties) >> error running from Secondary srv perspective
7. Elevated server level permissions/securables 1 by 1 >> error
8. Grant server role membership 1 by 1: bulkadmin, dbcreator ... >> error
9. Grant sysadmin >>> worked
uggggg
Many thanks. Jeff
September 10, 2014 at 10:52 am
Since alter should be adequate according to BOL, and the alter permission was inadequate, I would say it is a BOL error. Particularly based on those tests and results.
If you could provide more details so somebody else could replicate on their dev environment, I am sure we could probably confirm the entire method is sound or not, along with the results.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 10, 2014 at 1:02 pm
Hi SQLRNNR,
Here are the steps to setup testing environment. Let me know if anything else required.
Thanks again.
* SQL2008r2/SE/SP1; client tool: SQL Server Management Studio (SSMS)
* partner1/principal database: P1 (dev box); db1 (Primary db; Recovery Model=FULL)
* partner2/mirrored database: P2 (DR box); Failover db will be called db1 as well
* No witness server
* as sysadmin, setup dbs: backup/restore P1.db1 to P2.db1 (full/tlog); leave P2.db1 in restoring state;
* as sysadmin, initiate mirroring from P1.db1: use SSMS > P1.db1 properties > mirroring> Configure Security Wizard;
* as sysadmin, create low priv acct on P1 called "mirror_client"; DF db = db1; add "mirror_client" into db1; initially set to R/W - but elevated thru later testing to dbo - you can set to db_owner or ALTER here;
* as sysadmin, create low priv acct on P2 called "mirror_client"; DF db = master; note: P2.db1 database is inaccessible (restoring state); P2.db1 dbo is mapped to sysadmin acct used during earlier restore (verify: see P2.db1 properties);
* note: later tests, as sysadmin, I added acct "mirror_client" to P1.master and to P2.master (both db_owner);
* P1.db should be mirrored to P2.db1; as sysadmin, verify SSMS: P1.db1 (Principal, Syncronized), P2.db1 (mirror, syncronized, restoring ...)
* scenerio: need to run ALTER statement from P2 re: assume P1 box inaccessible due to sharknado
* use SSMS, log on to P2 as "mirror_client";
* as "mirror_client", run following statement:
use master;
ALTER DATABASE db1
SET PARTNER OFF;
go
* note: all tests (see earlier threads) errored from P2 unless "mirror_client" was granted sysadmin
* note: from P1, as "mirror_client", the ALTER statement worked if "mirror_client" set to dbo on P1.DB1
* I even tried wrapping the ALTER statement in SP (master db) and run SP as "mirror_client" (db_owner) but errored; "mirror_client" was able to run SP after being granted sysadmin
here is error message again:
Msg 927, Level 14, State 7, Line 1
Database 'db1' cannot be opened. It is in the middle of a restore
Many thanks. Jeff
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply