November 14, 2009 at 2:35 pm
im preparing to upgrade from sql server 2005 to 2008, and we have found that the best method (for us)
is a flattening and rebuild of the server with sql server 2008. the problem is when i script the logins on the server i only get the create statement for the login, but not all the permissions that login has on each database or what scheemas it has permissions on.
what are my options here, do i have to back up each of these permissions individualy, or is there an app/script i can download to do this, or can i just back up and restore the master/msdb databases?
November 15, 2009 at 7:52 pm
Are you planning to script out your database objects ? If yes, then this is where the permissions should be scripted - there is an option to script permissions ("Script object level permissions").
If you are simply going to backup/restore your user databases, then all you need to do is make sure that all logins get re-created with the same SID. For logins that are Windows users or groups, this is the only way since the SID from AD is used by SQL. For SQL Server Logins, you make sure the SID for each login is included when you add the login to the new server.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply