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

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

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