Drop Me? No, I Don't Think So

  • 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/.

  • nice question and great explanation..

    thanks Andy.

  • 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.

  • 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


    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.

  • Chris, thanks for posting that, nice to document that here.

  • 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.

  • 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!

  • 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.

  • This is a nice question. Something I learn today. Thanks.

  • Excellent QOTD....

  • This was removed by the editor as SPAM

  • More questions like these please, had to configure, drop, recreate, drop, recreate, fail to get to the answer. 😀

  • Good question and the effort to explain all incorrect answers are very much appreciated.

    Mel. 😎

  • +2 - thanks

Viewing 14 posts - 16 through 28 (of 28 total)

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