December 12, 2009 at 12:19 am
I need a small clarification.
Am restoring the database to a new instance say from 2000 and 2005.Now to fix the users, we are using the below script which will update the login or else it will generate sp_addlogin script.
/*********************************************************
--- Change_users_login.sql
**********************************************************/
SET NOCOUNT ON
PRINT '-- You are in ' + @@servername + '..' + db_name()
IF NOT EXISTS (SELECT 1 FROM sysusers WHERE issqluser = 1
and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null)
PRINT '-- All users are in synch in ' + @@servername + '..' + db_name()
ELSE
SELECTDISTINCT
CASE WHEN L.sid IS NOT NULL
THEN CHAR(13)+CHAR(10)
+ '-- User ' + COALESCE(U.[name], '[NULL]')
+ ' already exists on server'
+ CHAR(13)+CHAR(10)
ELSE CHAR(13)+CHAR(10)
+'EXEC sp_addlogin ''' + U.name + ''', ''password'', ''' + db_name() + ''''
+ CHAR(9)
+ '-- Only add if required (enter the correct password)!!'
+ CHAR(13)+CHAR(10)
END,
CHAR(13)+CHAR(10)
+'-- EXEC ' + db_name()
+ '.dbo.sp_dropuser @name_in_db = '
+ '''' + U.name + '''-- Remove this user if access is no longer required to this DB'
+ CHAR(13)+CHAR(10),
CHAR(13)+CHAR(10)
+'EXEC ' + db_name()
+ '.dbo.sp_change_users_login ''Update_One'', '
+ '''' + U.name + ''', '
+ '''' + U.name + ''''
+ CHAR(13)+CHAR(10)
FROMsysusers AS U
LEFT OUTER JOIN
(
sysmembers AS M
INNER JOIN sysusers AS G
ON G.uid = M.groupuid
) ON M.memberuid = U.uid
LEFT OUTER JOIN master.dbo.syslogins AS L
ON L.[name] = U.[name]
where U.islogin = 1
AND U.isaliased = 0
AND U.hasdbaccess = 1
AND
(
G.issqlrole = 1
OR G.uid IS NULL
)
AND U.name NOT IN ('dbo')
ORDER BY 1 DESC
SET NOCOUNT OFF
----------------------------------------------------------------------------------
Does this preserve the Server level permissions given to that login which was there in the old instance because the script is scripting only the sp_addlogin?
or else do we need to do anything more?
Any thoughts!
December 12, 2009 at 10:47 am
you need to set the correct default database, correct default language and reassign any server level permissions (sp_addsrvrolemember)
You would be better off using sp_help_revlogin. then the code you posted, there is a version to go from 2000 to 2005. This would set the correct default database, maintain the password and avoid SQL users becoming orphaned.
---------------------------------------------------------------------
December 12, 2009 at 12:45 pm
I'd agree with George that sp_help_revlogin is a better choice.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply