April 3, 2017 at 1:31 pm
Hello,
Background. I have a script to create/drop snapshot from a mirror. It is uses for creating reporting DB so that the report writer won't hit the PROD. I set up the schedule to update 3 times a day. However, the report writer requests a real time data and it got escalated to VP level. Now I am asked to give them a real-time data access.
My plan is to utilize my existing script and allow the report writer to run it anytime he want. I read one post that Gail answered about minimum permission to allow user to do so. (https://www.sqlservercentral.com/Forums/Topic1463643-1550-1.aspx). I granted user as dbcreator and ddladmin, and yet still got an error. The user can dropped a snapshot, but can't create one from a mirror DB. The error mentioned "The database "XYZ" cannot be opened. It is acting as a mirror database.". I checked permission in a mirror and set it correctly. Of course, it works when you are DBA. I am not sure what permission else to be granted.
Thank you in advance.
TS
April 3, 2017 at 1:35 pm
Of course, it works when you are DBA. I am not sure what permission else to be granted.
Are you saying that you created a snapshot of the mirror db (on the mirror side not the primary side) as a 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
April 3, 2017 at 2:15 pm
Jason - The snapshot of mirror DB is on one server and grant sysadmin permission to the user to make it work. I am not a fan of this route. Granting dbcreater and ddladmin works only dropping snapshot step, but doesn't work on creating snapshot. It threw me that error.
April 3, 2017 at 5:12 pm
thsiripa - Monday, April 3, 2017 2:15 PMJason - The snapshot of mirror DB is on one server and grant sysadmin permission to the user to make it work. I am not a fan of this route. Granting dbcreater and ddladmin works only dropping snapshot step, but doesn't work on creating snapshot. It threw me that error.
Sorry but that doesn't answer the question I posted which quoted you as saying it worked as DBA.
I am trying to get the facts straight here before jumping down a path or response that may not be accurate depending on your scenario.
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
April 3, 2017 at 8:51 pm
SQLRNNR - Monday, April 3, 2017 1:35 PMOf course, it works when you are DBA. I am not sure what permission else to be granted.
Are you saying that you created a snapshot of the mirror db (on the mirror side not the primary side) as a sysadmin?
Yes it worked when I created a snapshot of mirror DB on the mirror side as sysadmin.
April 4, 2017 at 11:18 am
Security
Permissions
Any user who can create a database can create a database snapshot; however, to create a snapshot of a mirror database, you must be a member of the sysadmin fixed server role.
https://msdn.microsoft.com/en-us/library/ms175876%28v=sql.120%29.aspx?f=255&MSPPError=-2147217396
There is the reason you create a snapshot on the mirror when you are a 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
April 4, 2017 at 12:51 pm
SQLRNNR - Tuesday, April 4, 2017 11:18 AMSecurity
Permissions
Any user who can create a database can create a database snapshot; however, to create a snapshot of a mirror database, you must be a member of the sysadmin fixed server role.
https://msdn.microsoft.com/en-us/library/ms175876%28v=sql.120%29.aspx?f=255&MSPPError=-2147217396
There is the reason you create a snapshot on the mirror when you are a sysadmin.
I see, Thanks for the info.
April 4, 2017 at 2:24 pm
You are welcome.
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
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply