CODEWITHSUNDEEP
sqlsql-serversql-server-2005
John Farrell
John Farrell

Reputation: 24754

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?

Upvotes: 3

Views: 30311

Answers (3)

user238571
user238571

Reputation: 11

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

Upvotes: 1

Kolten
Kolten

Reputation: 3503

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.

Upvotes: 3

littlechris
littlechris

Reputation: 4184

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.

Upvotes: 2

Related Questions