Fix Orphaned Users - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Fix Orphaned Users

Author

Topic

chadmat
The Chadinator

1974 Posts

Posted - 2002-02-04 : 13:30:05

/*************************************************************************************
This procedure should be created in the Master database. This procedure takes no
parameters. It will remap orphaned users in the current database to EXISTING logins
of the same name. This is usefull in the case a new database is created by restoring
a backup to a new database, or by attaching the datafiles to a new server.
*************************************************************************************/

IF OBJECT_ID('dbo.sp_fixusers') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.sp_fixusers
IF OBJECT_ID('dbo.sp_fixusers') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_fixusers >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.sp_fixusers >>>'
END

GO

CREATE PROCEDURE dbo.sp_fixusers

AS

BEGIN

DECLARE @username varchar(25)

DECLARE fixusers CURSOR
FOR

SELECT UserName = name FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
ORDER BY name

OPEN fixusers

FETCH NEXT FROM fixusers
INTO @username

WHILE @@FETCH_STATUS = 0
BEGIN
EXEC sp_change_users_login 'update_one', @username, @username
FETCH NEXT FROM fixusers
INTO @username
END

CLOSE fixusers
DEALLOCATE fixusers
END
go
IF OBJECT_ID('dbo.sp_fixusers') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.sp_fixusers >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_fixusers >>>'
go

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-02-05 : 15:37:53

Chad,

Thanks for the script. I executed it in master to create the sproc, and then switched to my newly attached database and did exec sp_fixusers and got the following error message:

quote:

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

This is a new SQL 2000 / Windows 2000 AS machine. I ran the script from MS's KB article to "Transfer Logins and Passwords", then detached the files from SQL 7, attached to SQL2K, ran sp_updatestats then got to your script.

What did I do wrong?

<edit>
BTW, I just did an sp_helplogins and see that the SID for sa is 0x01. Do I need to change your script?
</edit>
--------------------------------
There's a new General in town...

Edited by - AjarnMark on 02/05/2002 15:40:03

chadmat
The Chadinator

1974 Posts

Posted - 2002-02-05 : 16:02:47

No,

The script will fix all users EXCEPT sa-dbo.

Run: sp_changedbowner 'sa'

This will map dbo back to the sa login.

Sorry, I should have mentioned that in the original post. sp_change_users_login does not work on dbo. I guess I could change the script to handle that user/login pair, I just never did that (It was easy enough to run sp_changedbowner).

If you run: sp_change_users_login 'report'
before you run my procedure, you will see all of your orphaned users, then after you run my procedure, run sp_change_users_login 'report'
agian, and you will see that there are no more (Except dbo) after you run sp_changedbowner, if you run sp_change_users_login 'report'
again there should be nothing listed.

HTH
-Chad

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-02-05 : 17:16:18

Yep, that was the trick. Takes me back to my post a couple weeks ago about who should be listed as db owner, which I had not yet gotten around to cleaning up my info.

THANKS!! It works great!!

--------------------------------
There's a new General in town...

chadmat
The Chadinator

1974 Posts

Posted - 2002-03-15 : 14:56:56

I modified this script to handle the 'dbo' user. So yo no longer have to run sp_changedbowner 'sa' before (Or after) running this proc.

/*************************************************************************************
This procedure should be created in the Master database. This procedure takes no
parameters. It will remap orphaned users in the current database to EXISTING logins
of the same name. This is usefull in the case a new database is created by restoring
a backup to a new database, or by attaching the datafiles to a new server.
*************************************************************************************/

IF OBJECT_ID('dbo.sp_fixusers') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.sp_fixusers
IF OBJECT_ID('dbo.sp_fixusers') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_fixusers >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.sp_fixusers >>>'
END

GO

CREATE PROCEDURE dbo.sp_fixusers

AS

BEGIN

DECLARE @username varchar(25)

DECLARE fixusers CURSOR
FOR

SELECT UserName = name FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null
ORDER BY name

OPEN fixusers

FETCH NEXT FROM fixusers
INTO @username

WHILE @@FETCH_STATUS = 0
BEGIN
IF @username='dbo'
BEGIN
EXEC sp_changedbowner 'sa'
END
ELSE
BEGIN
EXEC sp_change_users_login 'update_one', @username, @username
END
FETCH NEXT FROM fixusers
INTO @username
END

CLOSE fixusers
DEALLOCATE fixusers
END
go
IF OBJECT_ID('dbo.sp_fixusers') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.sp_fixusers >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_fixusers >>>'
go

aaliang
Starting Member

1 Post

Posted - 2003-04-25 : 14:22:18

Add my two cents:
1. To fix the dbo issue, rewrite the select by adding the one line. It takes care of the dbo user issue since dbo is not a login

SELECT UserName = name FROM sysusers
WHERE issqluser = 1 and (sid is not null and sid <> 0x0)
and suser_sname(sid) is null

and name in(select name from master.dbo.syslogins)

ORDER BY name

2. It doesn't make much sense to me to create a stored proc

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-25 : 14:33:09

OR...

SET QUOTED_IDENTIFIER OFF
GO

DECLARE @SQL varchar(100)

DECLARE curSQL CURSOR FOR
select "exec sp_change_users_login 'AUTO_FIX','" + name + "'"
from sysusers
where issqluser = 1 and name NOT IN ('dbo', 'guest')

OPEN curSQL

FETCH curSQL into @SQL

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

CLOSE curSQL
DEALLOCATE curSQL

go

I recently had to run this script in production when we moved our production databases to our disaster recovery site (just for testing) and received a few errors about duplicate SIDs. I modified it so that it uses UPDATE_ONE instead of AUTO_FIX and it worked perfectly. I do not have the modified script in front of me, but I think that anyone (with dynamic SQL knowledge) would be able to modify it pretty easily.

Tara

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-04-25 : 15:35:29

I'm confused Tara. Isn't you approach essentially the same as Chad's? What are you adding to make it better?

Jay White
{0}

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-25 : 15:36:38

quote:

I'm confused Tara. Isn't you approach essentially the same as Chad's? What are you adding to make it better?

Mine just has less lines of code, that's all. Same thing though like you said.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-28 : 12:54:45

One more for the toolbox....thank you very much...

Brett

8-)

chadmat
The Chadinator

1974 Posts

Posted - 2003-04-29 : 00:07:31

quote:
2. It doesn't make much sense to me to create a stored proc

I use it as a stored proc on systems that we move databases to a lot. That way I don't have to open a script file every time. Of corse it isn't required, modify it as needed.

The DBO issue should be fixed in my second posting, I use it all the time, and have'nt ever had any problems.

quote:
I modified it so that it uses UPDATE_ONE instead of AUTO_FIX and it worked perfectly.

I thought I did use UPDATE_ONE.

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-29 : 12:19:41

quote:

quote:
I modified it so that it uses UPDATE_ONE instead of AUTO_FIX and it worked perfectly.

I thought I did use UPDATE_ONE.

-Chad

I was referring to the one that I posted and not yours.

Tara

Kristen
Test

22859 Posts

Posted - 2006-05-02 : 05:52:14

We often move databases from Client to DEV, and vice-versa, and want to reinstate the appropriate users, but often to DROP users that are not appropriate and so on.

This script generates a script to sort out the logins. It does the following:

If user does NOT have a login create one using sp_addlogin (you will need to provide a password)

If user DOES have a login then report the fact and offer to drop from DB using sp_dropuser

Then use sp_change_users_login to synchronise the user with the Login ID.

The script can then be hand edited as appropriate, and run.


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	sysusers 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] 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')

Sample output


-- User USER1 already exists on server
-- Remove this user if access is no longer required to this DB
-- EXEC MyDatabase.dbo.sp_dropuser @name_in_db = 'USER1'
EXEC MyDatabase.dbo.sp_change_users_login 'Update_One', 'USER1', 'USER1'

-- Only add if required!!
EXEC sp_addlogin 'USER2', 'password', 'MyDatabase'
-- Remove this user if access is no longer required to this DB
-- EXEC MyDatabase.dbo.sp_dropuser @name_in_db = 'USER2'
EXEC MyDatabase.dbo.sp_change_users_login 'Update_One', 'USER2', 'USER2'

Kristen

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2006-06-18 : 08:48:47

hi there ........which script to use.
I have 100 databases on server 1 and restored 4 of them on new server so i need to bring only need to update logins on these databases.

I did the restores....so which script do i run and on which server...
i need to bring across the logins and passwords.

there maybe some logins on server1 that access database which i did not restore.

Please advise.....thank you.

Kristen
Test

22859 Posts

Posted - 2006-06-25 : 02:11:24

See also:

http://www.support.microsoft.com/?id=246133 How To Transfer Logins and Passwords Between SQL Servers (for SQL 7/2000)
http://support.microsoft.com/kb/918992 (for SQL 2005)
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a Restore
http://www.sqlmag.com/articles/index.cfm?articleid=16090 SQL Server Login Transfer

Kristen

jn148
Starting Member

1 Post

Posted - 2006-11-21 : 11:03:15

Thanks for the SQL2005 specific link to Microsoft KB Kristen!

Though... any advice for someone who was dumb enough to have formatted the old SQL2005 server and all we have is a backup file of the database?

Thanks!

Kristen
Test

22859 Posts

Posted - 2006-11-21 : 12:39:48

You can make a list of UserIDs from the database [once restored] - but not the original passwords of course!

Kristen

Anjumc
Starting Member

1 Post

Posted - 2007-01-31 : 09:00:27

quote:
Originally posted by Kristen

You can make a list of UserIDs from the database [once restored] - but not the original passwords of course!

Kristen

Kristen,

I'm very new to SQL server. Can you please help me?
I have few databases on Windows NT and SQL Server 7 which I want to move to SQL Server 2005 and Windows 2003.
Can you please help me in the steps how to proceed?
I have already installed the SQL Server 2005.

Thanks a lot
ANjum

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-08-04 : 08:44:36

Hi kristen

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 sysusers 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] 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')

Does you have one that works with SQL 2005.
I just restored db from 2000 to 2005 and need to do the orphans but not one by one i be there all day

Cheers

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-04 : 20:11:54

What's your problem? Did you get any error?

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2007-08-05 : 07:13:41

The syslogins is not in SQL 2005

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources