May 14, 2014 at 6:55 am
We'll have to talk to Steve about a calendar. I miss the Stumpers myself - it's been a while since a version was published, you can get it free here at http://www.sqlservercentral.com/articles/books/67141/.
May 14, 2014 at 7:45 am
nice question and great explanation..
thanks Andy.
May 14, 2014 at 8:06 am
Great question and explanation!
I am more of a developer than a DBA and the more time I spend on this site, the more I realize how little I know.
May 14, 2014 at 10:55 am
Great question!
As for the endpoint configuration I can confirm that yes, endpoints are created with the principal_id of the login creating them; we had a situation where a person left the company but he had created the mirroring endpoint so we couldn't drop the login until that was resolved. To see who owns the endpoints you can run:
[font="Courier New"]SELECT e.name
, sp.name
FROM sys.endpoints AS e
INNER JOIN sys.server_principals AS sp
ON sp.principal_id = e.principal_id
[/font]
There are two basic ways around it: 1) Run the CREATE ENDPOINT command as the server_principal you want to own it (e.g. as 'sa') via connecting as the server_principal or running "EXECUTE AS LOGIN = 'sa'" prior to the CREATE command and REVERTing afterwards; or 2) Alter the authorization of the endpoint after it's created and change it to the server_principal you prefer "ALTER AUTHORIZATION ON ENDPOINT::Database_Mirroring TO sa". Altering the authorization apparently doesn't interfere at all with the traffic over the endpoint as nothing negative happened when we ran it.
May 14, 2014 at 12:21 pm
Chris, thanks for posting that, nice to document that here.
May 14, 2014 at 1:01 pm
Awesome, this was really cool post. I narrowed down to 2 and 6, and then was thinking if the db was offline then how server will access it... then with my limited understanding, I guessed correctly. This is one of the area I need to focus a lot and quickly.:-)
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
May 14, 2014 at 3:14 pm
I didn't put enough thought into this one. I was leaning towards the login having granted permissions to another login and then took the bait on the offline databases. Should have thought about it a little more. Oh well, good question and very thorough explanation. Thanks Andy!
May 14, 2014 at 9:37 pm
I love security questions; I've run into this very situation several times, usually with a rogue login outside of my team's active directory group where they've setup database mirroring and used their login to assign permissions to an endpoint.
May 14, 2014 at 11:19 pm
This is a nice question. Something I learn today. Thanks.
May 14, 2014 at 11:21 pm
Excellent QOTD....
May 15, 2014 at 1:28 am
This was removed by the editor as SPAM
May 15, 2014 at 3:25 am
More questions like these please, had to configure, drop, recreate, drop, recreate, fail to get to the answer. 😀
May 21, 2014 at 6:14 am
Good question and the effort to explain all incorrect answers are very much appreciated.
---------------
Mel. 😎
January 26, 2015 at 12:24 pm
+2 - thanks
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply