Tuesday, May 3, 2011

Easy way to import/export Sql Server 2005 users/permissions across instances?

Is there an easy way to export and then import users/permissions from one Sql Server 2005 instance to another?

From stackoverflow
  • Use the management console to generate a script for your users and thier associated permissions. Same can be done for the server logins in the security folder.

    Select your user: Select DB and expand security\users folder Right Click --> Script User As --> Create To --> New Query

    For Logins

    Select your Login: Expand security\logins folder Right Click --> Script Login As --> Create To --> New Query

    Now just run the query on your new instance. Just choose the db to run the script in.

  • Might need to do a "sp_change_users_login AUTO_FIX, 'my_user'" afterwards to re-link security logins to their respective data as well.

  • sqlcmd -E -d master -S suksql01 -Q "sp_help_revlogin" -o \suksql03\login\LOGIN.txt go exec sp_help_revlogin go

    exec sp_help_login

0 comments:

Post a Comment