Post reply

Connection Problems

  • February 6, 2011 at 7:24 am

    #246233

    Comments posted to this topic are about the item Connection Problems

  • February 6, 2011 at 11:03 am

    #1282893

    Very good scenario based question.

    M&M

  • February 6, 2011 at 2:17 pm

    #1282901

    Good Question.

    Thanks.

  • February 6, 2011 at 11:01 pm

    #1282932

    Good question.

    🙂

  • February 6, 2011 at 11:58 pm

    #1282944

    Great question, thanks!

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • February 7, 2011 at 12:53 am

    #1282954

    Thank you very much for this question and scenario--and for the update regarding the sp_change_users_login stored procedure.

    Regards,

    Michael

  • February 7, 2011 at 1:16 am

    #1282961

    This is an old problem with sql2000 too: restoring a database on a different instance, logins to database become invalid.

    The solution is create login on the restoring instance with the same sid of the source instance

    See here: http://msdn.microsoft.com/en-us/library/ms189751.aspx

    CREATE LOGIN loginName { WITH <option_list1> | FROM <sources> }

    <option_list1> ::=

    PASSWORD = { 'password' | hashed_password HASHED } [ MUST_CHANGE ]

    [ , <option_list2> [ ,... ] ]

    <option_list2> ::=

    SID = sid | DEFAULT_DATABASE = database

    | DEFAULT_LANGUAGE = language

    | CHECK_EXPIRATION = { ON | OFF}

    | CHECK_POLICY = { ON | OFF}

    | CREDENTIAL = credential_name

    <sources> ::=

    WINDOWS [ WITH <windows_options>[ ,... ] ]

    | CERTIFICATE certname

    | ASYMMETRIC KEY asym_key_name

    <windows_options> ::=

    DEFAULT_DATABASE = database

    | DEFAULT_LANGUAGE = language

  • February 7, 2011 at 1:20 am

    #1282965

    Exactly a scenario that happens in our environments. Thanks for the question!

    Ronald HensbergenHelp us, help yourself... Post data so we can read and use it: http://www.sqlservercentral.com/articles/Best+Practices/61537/-------------------------------------------------------------------------2+2=5 for significant large values of 2

  • February 7, 2011 at 1:36 am

    #1282977

    Thanks for the kind comments everyone - this was my first question, so it's good to get a positive response!

    And thanks Carlo for the extra information - I wasn't previously aware you could do that.

  • February 7, 2011 at 2:15 am

    #1282990

    I always use sp_change_users_login in these circumstances, so had to pick an answer at random, luckily I got the right one :).

    I wonder why they are withrawing this procedure? Anyone know if the replacement command does all the security checking that the procedure does?

  • February 7, 2011 at 2:24 am

    #1282992

    What security checking does sp_change_users_login do?

    All it does is map a database user with a login. BOL says nothing about any security checking.

  • February 7, 2011 at 2:27 am

    #1282994

    sp_helptext[sp_change_users_login] will reveal all 🙂

  • February 7, 2011 at 2:36 am

    #1282998

    Nice question, but very easy indeed for those of us who have been moving databases from server to server for more than a decade.

    Tom

  • February 7, 2011 at 3:01 am

    #1283001

    Toreador (2/7/2011)

    sp_helptext[sp_change_users_login] will reveal all 🙂

    Presumably the permissions on ALTER USER will perform the required security checks on whether the user has permission to perform the action. The sp_change_users_login does seem to do quite a lot, but apart from listing the orphaned users in a database it should be possible to do everything with CREATE LOGIN and ALTER USER.

  • February 7, 2011 at 3:05 am

    #1283004

    Nils Gustav Stråbø (2/7/2011)

    What security checking does sp_change_users_login do?

    All it does is map a database user with a login. BOL says nothing about any security checking.

    It does some checks on the user that is running the procedure. If it's not a member of the db_owner group it won't let you report or update one, and if the user isn't a member of sysadmin it won't let you auto fix.

Viewing 15 posts - 1 through 15 (of 39 total)

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