November 13, 2008 at 11:33 am
Hi
we are doing log of database migrations , so it is making lot of time and pain while restoring users back to the databases
what is the best way to keep users intact with data Move.
or how i can save all users before i restore or migrate them.
Here is my intension is this , in step 1 save all users in one place and restore them after database refreshed or moved
NEED HELP ON BOTH 2000 AND 2005
November 13, 2008 at 2:31 pm
If you move a database by backing it up and restoring it, the users go with it. If you're moving databases to a different instance, you'll need to copy the logins and sync the users and logins by running sp_change_users_login.
I've used Microsoft's sp_help_revlogin dozens of times to migrate logins. It's described in this KB article: http://support.microsoft.com/kb/246133/
Greg
November 13, 2008 at 2:48 pm
After users are moved make use of below mentioned stored procedure for resolving orphan user problem:
CREATE PROCEDURE SP_AUTOFIX_SQL_USERS_FOR_ALL_DBS
AS
declare @Dbname Varchar(100)
DECLARE Database_cursor CURSOR FOR Select name from sysdatabases Where dbid NOT IN (1, 2, 3, 4, 5, 6)
OPEN Database_cursor
FETCH NEXT FROM Database_cursor INTO @Dbname
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @Dbname
EXEC ('USE ' + @dbname + ' set nocount on
declare @login sysname
PRINT DB_NAME()
PRINT ''--------'' DECLARE user_update_cursor CURSOR FOR
SELECT distinct name from sysusers where issqluser = 1 and hasdbaccess=0 and name not in (''dbo'', ''guest'') and name in (select name from master..sysxlogins) order by name
OPEN user_update_cursor
-- Perform the first fetch and store the values in variables.
-- Note: The variables are in the same order as the columns
-- in the SELECT statement.
FETCH NEXT FROM user_update_cursor
INTO @login
-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
print @login
exec sp_change_users_login ''update_one'', @login, @login
--This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM user_update_cursor INTO @login
END
CLOSE user_update_cursor
DEALLOCATE user_update_cursor')
FETCH NEXT FROM Database_cursor INTO @Dbname
END
CLOSE Database_cursor
DEALLOCATE Database_cursor
HTH!
MJ
November 13, 2008 at 3:37 pm
Are you moving to new places or to the same server over and over?
You only need to move logins once, and if you use the same SID, then you don't have to sync them. If you create the users without using the same SIDs, then you will also need to sync back up users with sp_change_users_login after each restore.
Note that the sp_help_revlogin on the MS site is only for 2000. They rev'd it and I haven't see the 2000 version on the ms.com site. There is a copy here on this site if you search for it.
November 13, 2008 at 5:29 pm
Actually sp_help_revlogin works for SQL 2005 also. There's a section in the kb article I mentioned that has a link to another article that has the text of the sp for 2005. I just listed 245133 because the OP said he needed info for both versions.
Greg
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply