Access Granted or Not?

  • Great question & explanation, thanx Andy 🙂

    Thanks & Best Regards,
    Hany Helmy
    SQL Server Database Consultant

  • Thanks for all the comments, I appreciate the feedback!

  • great QOTD..

    thanks Andy.

  • A good question, but I found it somewhat irritating because something completely irrelevant was made part of the answer.

    By examing the various statements in the question, it is possible to eliminate orphaniing (the sid of the database user is the same as the sid of the login) which instantly renders any report from SP_change_users_login completely irrelevant, so I dropped that from my consideration straight away. Since the group login worked (for another database) on this instance, it was obvious that the groups connect permission for this database had been cut off although the database user had not been deleted, so I knew what teh problem was (and how to fix it). But I was then left to try to work out which of the other options could be intended as the second answer. I thought that irrelevant nonsense wouldn't be part of the answer, and as all the other options appeared to be false none of them seemed to be part of teh answer either. so I picked one of them at random, to find out what the right answer was, and was irritated to discover that the obviously irrelevant stuff was actually the second part of the answer.

    Interesting that the SP is deprecated and will disappear some time. Even more interesting that the designated replacement, ALTER USER, is even less capable than the SP so far as finding orphaned users is concerned - something that does part of the job is being deprecated with an intended replacement that does none of the job. That doesn't worry me, as simple queries on sys.database_principals and sys.server_principals can easily do more than can be done with the SP, and certainly ALTER USER is certainly a better way of dealing with the other functions of the SP than the SP is, but it does seem a bit strange and I hope Ms won't make a habit of taking away facilities without designating an effective replacement (probably a folorn hope, but must avoid despair).


Viewing 5 posts - 16 through 19 (of 19 total)

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