SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Fix Orphaned Users		  New Topic  Reply to Topic
 Printer Friendly
Previous Page
Author Previous Topic Topic Next Topic
Page: of 2

tkizer
Almighty SQL Goddess

USA
35017 Posts
Posted - 08/05/2007 :  14:15:55  Show Profile  Visit tkizer's Homepage  Reply with Quote
Here is what I use for 2005:


DECLARE @SQL VARCHAR(100)

DECLARE curSQL CURSOR FOR
	SELECT 'EXEC sp_change_users_login ''UPDATE_ONE'', ''' + name + ''', ''' + name + ''''
	FROM sysusers
	WHERE issqluser = 1 AND name NOT IN ('guest', 'dbo', 'sys', 'INFORMATION_SCHEMA')

OPEN curSQL

FETCH curSQL INTO @SQL

WHILE @@FETCH_STATUS = 0
BEGIN
	EXEC (@SQL)
	FETCH curSQL INTO @SQL
END

CLOSE curSQL
DEALLOCATE curSQL

GO


Tara Kizer
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

alexsqlforum
Starting Member

USA
3 Posts
Posted - 08/27/2007 :  14:12:51  Show Profile  Reply with Quote
Here is the script for sql 2005


SELECT DISTINCT
CASE WHEN L.sid IS NOT NULL
THEN '-- User ' + COALESCE(U.[name], '[NULL]') + ' already exists on server'
ELSE 'EXEC sp_addlogin ''' + U.name + ''', ''password'', ''' + db_name() + ''''
+ CHAR(9) + '-- Only add if required!!'
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)+'EXEC ' + db_name()
+ '.dbo.sp_change_users_login ''Update_One'', '
+ '''' + U.name + ''', '
+ '''' + U.name + ''''
FROM sys.sysusers AS U
LEFT OUTER JOIN
(
sys.sysmembers AS M
INNER JOIN sys.sysusers AS G
ON G.uid = M.groupuid
) ON M.memberuid = U.uid
LEFT OUTER JOIN sys.sysusers AS L
ON L.[name] = U.[name] COLLATE SQL_Latin1_General_CP1_CI_AS
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')
Go to Top of Page

TRACEYSQL
Aged Yak Warrior

576 Posts
Posted - 08/27/2007 :  20:06:22  Show Profile  Send TRACEYSQL an ICQ Message  Reply with Quote
Let me try that script in 2005
I restore a 2000 db and then run the script to see if it creates the db user name login

Thanks
Go to Top of Page

hey001us
Posting Yak Master

185 Posts
Posted - 12/04/2007 :  17:54:30  Show Profile  Reply with Quote
EXEC sp_change_users_login 'update_one', 'dbo', 'DYNSA'

I am getting follwing error. any advice?

Server: Msg 15287, Level 16, State 1, Procedure sp_change_users_login, Line 39
Terminating this procedure. 'dbo' is a forbidden value for the login name parameter in this procedure.

hey
Go to Top of Page

nancytx
Starting Member

USA
1 Posts
Posted - 01/14/2010 :  10:58:04  Show Profile  Reply with Quote
The table to use in SQL 2005 is sys.syslogins...raplace that in your script and it will run fine on 2005.
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Previous Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000