I am moving a database from a SQL 2000 server to a new SQL 2005 server. I ran a script that created all of the logins in security/logins on the 2005 server and retained the passwords. What I don't have however is access to the database on the new server. The database on the new server has a Database role called 'Elite'. Essentially, I need to find a scripted way of granting the over 2000 objects in security/logins access to a database called 'son_db'...as well as give them access to the role called 'Elite' in that database.
I came up with the following, but so far it's not working....I may be on the wrong track here so I'd appreciate any help that could be provided!
Select [Name] INTO UserList From Master..SysLogins
DECLARE aCURSOR Cursor
Select Name From UserList
DECLARE @Name NVarChar(20)
While (@@FETCH_STATUS <> -1)
Fetch NEXT FROM aCursor INTO @Name
EXEC sp_grantdbaccess 'son_db', @Name
EXEC sp_addRoleMember 'Elite', @Name
Drop Table UserList