question

noblethraher avatar image
noblethraher asked

Migrating Persmissions along with Logins to new SQL Server Instance...

I am migrating a set of databases to a new SQL Server instance. Backing up and restoring the databases worked just fine, as did migrating logins with sp_help_revlogin however none of the permissions for the logins were preserved. Is there a way to migrate the permissions along with the logins?

sql-server-2005restoreloginmigration
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
TG avatar image
TG answered

The permissions should be with the databases. Perhaps you've lost the association between the server logins and the database users. See if their SIDs are different. If so you can fix them with 

ALTER USER <user> with login = <associated login>

or

exec sp_change_users_login (check books online for how to use this)

http://technet.microsoft.com/en-us/library/ms174378.aspx

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.