We're finally moving from SQL 2000 to SQL 2008. One challenge we've faced
in our test upgrades is that our users/permissions are not set in SQL 2008.
Is this because we're not setting our users up before we restore our db into
SQL2008?

Also, is there a way to script out our SQL 2000 users and all their
permissions in the various dbs they have access to? Not only will this help
with our migration, but it will be nice to have as a backup as well.

Thanks in advance.

Andre

Andre,

This is probably what you need.
http://support.microsoft.com/kb/246133

When you restore databases, you bring across the users and their rights.
What is missing is the logins that match the users. By creating the logins,
the users should automatically hook up to the proper accounts, provided that
you use the proper options. Notice that if you are moving Windows logins
from one domain to another, there are extra complications. It is all in
the document.

RLF

"Andre" <no (AT) spam (DOT) com> wrote

This transfers the logins, but it doesn't appear to set the permissions. I
don't see how this is any better than using the SSIS Transfer Logins task.
Do I need to have the logins in place before I restore a db in order for the
permissions to be set?

Andre

Permissions are not set on the login - they are set on the user, which is
stored in the database. So, the permissions should be restored when the
database is restored and the login created. If the login is already created
when the database is restored (with the right SID), then there shouldn't be
anything else needed.

"Andre" <no (AT) spam (DOT) com> wrote

Ok, got it. Thanks.