Is there a way of copying the securables definitions from 1 db to another?

  • We're finally moving to SQL 2012 from SQL 2005. Is there a way of automating the moving or copying of securables from the old database for some of the users, to the new database for the same users? If so, how is that done?

    Kindest Regards, Rod Connect with me on LinkedIn.

  • No. What procedures are you taking to move over your databases from 05 to 12? Backup and restore I hope!

    MCSE SQL Server 2012\2014\2016

  • Rod at work (9/5/2013)


    We're finally moving to SQL 2012 from SQL 2005. Is there a way of automating the moving or copying of securables from the old database for some of the users, to the new database for the same users? If so, how is that done?

    As "I.T.D." said, backup old DBs, then restore them to new server instance. If the server logins that relate to the DB Users exist on the destination when the restore takes place, the securables will align with those logins. Otherwise they will be orphaned. When the appropriate logins are added after the restore, they can be quickly fixed by use of

    [font="Courier New"] sp_Change_Users_Login 'Auto_Fix', 'UserName'[/font]

    Mike Hinds Lead Database Administrator1st Source BankMCP, MCTS

  • Rod, did you understand what Mike is saying? Please PM me if you need more explaination. 🙂

    MCSE SQL Server 2012\2014\2016

  • Hi Mike and It's The Database,

    Your reply, Mike, made me realize that I left out some important details. Our current SQL 2005 database is on a server on-premise. The new SQL 2012 database will be on a VM, on a server hosted by our parent company. So the hardware aspect of our operation is moving off site. I'm pretty sure that those users and accounts will at least change names, on the new machine/VM.

    Kindest Regards, Rod Connect with me on LinkedIn.

  • Rod,

    you can one of two things here.

    a) create the current users under security and once you restore they will sync up.

    b) new site new names, no worries, restore the database, delete the old users from the database then add the new user names in security.

    pretty simple

    MCSE SQL Server 2012\2014\2016

Viewing 6 posts - 1 through 5 (of 5 total)

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