locked
access issue rev_login RRS feed

  • Question

  • Hi experts,

     

    I ran rev_login, to migrate logins for a migration (side by side).

     

     

    But when I execute it in the target server, it executes:

     

    -- Login: xyzz

    CREATE LOGIN [xyzz] WITH PASSWORD = XXXX HASHED, SID = XXXXX, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

     

    It creates the login, but that user has ‘read access’ on every database, when does that ‘read-access’ gets mapped to the login?

    Friday, June 23, 2017 10:19 AM

All replies


  •  

    It creates the login, but that user has ‘read access’ on every database, when does that ‘read-access’ gets mapped to the login?

    Sp_help_Revlogin SP ONLY migrates the logins with password it does not migrates or provides the rights the login had in previous instance. You have to manually do it, below would help you

    Transferring Logins with Permission


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Friday, June 23, 2017 12:01 PM
  • Hi,

    rev_login will migrate only LOGINS but not USERS.

    If you want to map the Login: xyzz for all the databases manually to ‘read-access' if those databases created new server.

    If those database are migrated different server, the users of that database will move to this server but LOGINS will not come along with the database migration.

    If you think, the login: xyzz is available at server level and it has been mapped with one of the database and user not able to query. Then you need to verify the orphan users.

    It creates the login, but that user has ‘read access’ on every database, when does that ‘read-access’ gets mapped to the login?

    This one you can verify only when the user database migrated from different server, if the database is newly created on new server you need to map the login to the database with necessary permissions.


    Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

    Friday, June 23, 2017 3:11 PM
  • Hi,

    rev_login will migrate only LOGINS but not USERS.

    If you want to map the Login: xyzz for all the databases manually to ‘read-access' if those databases created new server.

    If those database are migrated different server, the users of that database will move to this server but LOGINS will not come along with the database migration.

    If you think, the login: xyzz is available at server level and it has been mapped with one of the database and user not able to query. Then you need to verify the orphan users.

    It creates the login, but that user has ‘read access’ on every database, when does that ‘read-access’ gets mapped to the login?

    This one you can verify only when the user database migrated from different server, if the database is newly created on new server you need to map the login to the database with necessary permissions.


    Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

    Satish I hope you have read the question OP is asking how to migrate login permissions and regarding users you actually never will have to move it because when you restore the database the users are migrated

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Sunday, June 25, 2017 6:15 AM
  • Satish I hope you have read the question OP is asking how to migrate login permissions and regarding users you actually never will have to move it because when you restore the database the users are migrated


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Once the login migration did., think like if the database is newly created on new server? can the login map automatically to the new database?

    Hence I said all the possibilities here.


    Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)



    Sunday, June 25, 2017 7:09 PM
  • Once the login migration did., think like if the database is newly created on new server? can the login map automatically to the new database?

    Hence I said all the possibilities here.

    He was not asking about mapping, was he ?. He was asking that even if I use revlogin SP why is *permission* not being moved and to which I said the SP just moves logins I dont know from where users came.

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Monday, June 26, 2017 6:20 AM
  • Once the login migration did., think like if the database is newly created on new server? can the login map automatically to the new database?

    Hence I said all the possibilities here.

    He was not asking about mapping, was he ?. He was asking that even if I use revlogin SP why is *permission* not being moved and to which I said the SP just moves logins I dont know from where users came.

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    I don't think so this is the right place to make debue with each other., Just I said to the requester after the login migration, we need take care of user level permissions at database level. He said he already did the login migration and he is/was asking about "when does the read access get mapped to login"., see the below screen shot. " the read-access for USERS only NOT FOR LOGINS"  As every one know the sp_revlogin will do transfer only logins not permissions at the database. Hence I said about users in my earlier post...


    Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)


    Monday, June 26, 2017 12:59 PM
  • The users permissions, either role_membership or on a per object basis are stored in individual databases, and not stored in the master database - which is where sp_help_revlogin gets its information from.

    If you have restored the databases to the new server, the permissions and databases users will be there.

    You can check to ensure that the logins are not orphaned in the user databases by running

    sp_change_users_login 'report' in each database.

    Monday, June 26, 2017 1:29 PM
  • Well you can debate here, forum has no such restriction but I agree that would make things off topic . So I am, as well, not going to take it further but my whole motive was "to keep answer short instead giving all the history"

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Monday, June 26, 2017 6:28 PM