Post reply

SQL Server backup and restore across different authentication models

  • October 22, 2014 at 9:52 am

    #291849

    Hey Guys,

    We have a bunch of SQL 2012 databases which use SQl Server authentication (essentially local dev instances). Does anyone know if it's possible to take a backup of one of these database and then push them onto a (central) server which uses Integrated security (based on active directory authentication) using a script to change and map the authentication model in the process?

    Thanks

    Nick

  • October 22, 2014 at 12:29 pm

    #1754154

    I'm not sure what problem you would have doing that. The setting is an instance, not database level setting...when you restore the database backup that came from a "mixed mode" server to an NT-only server, the native users will create but they'll probably be orphaned users, showing up as "SQL user without login" in the GUI.

    I guess you could drop those users and map out new NT based login/users to your database as part of a script. But there is no (that I'm aware of) database level setting for authentication mode, that is chiefly an instance setting and enforces against connections at a login level.

Viewing 2 posts - 1 through 1 (of 1 total)

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