How to fix dbo, guest, or INFORMATION_SCHEMA users

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

SQL Server 2000 Forums

SQL Server Administration (2000)

How to fix dbo, guest, or INFORMATION_SCHEMA users

Author

Topic

kamesh.pasupuleti
Starting Member

6 Posts

Posted - 2007-08-06 : 08:48:04

Hi All

I am Junior DBA.
I have moved all the logins from one server to another server.
After that I have fixed all the users with related logins on new server by using "sp_change_users_login" stored procedure.

But I have to users with the User_ids 3 and 4, I am not able to fix these users by using that SP "sp_change_users_login".

Is there any other way to fix these users.
Please help me........

Thanks in advance......

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-06 : 09:30:36

why can't you fix them?
is there an error you get?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

kamesh.pasupuleti
Starting Member

6 Posts

Posted - 2007-08-06 : 09:36:26

yes I am gettig following error

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

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-08-06 : 12:41:37

What was your whole statement? Did you try to fix trusted sql login?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-08-06 : 13:55:01

Here's what I use to unorphan accounts in 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 ('INFORMATION_SCHEMA', 'sys', 'guest', 'dbo')

OPEN curSQL

FETCH curSQL INTO @SQL

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

CLOSE curSQL
DEALLOCATE curSQL

Tara Kizer
http://weblogs.sqlteam.com/tarad/

kamesh.pasupuleti
Starting Member

6 Posts

Posted - 2007-08-09 : 03:11:19

Hi Rmiao

The following statement I am executing to fix those logins

EXEC sp_change_users_login 'UPDATE_ONE','Usere_name','LoginName'

Hi tkizer

I think you gave me the script which is used to fix other than 'INFORMATION_SCHEMA', 'sys', 'guest', 'dbo' users.

I need the script to fix the 'INFORMATION_SCHEMA', 'sys', 'guest', 'dbo' users.

Please help me...........

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-08-09 : 11:44:12

No you don't need to fix those! Just use my script to fix the user accounts, not system accounts.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

kamesh.pasupuleti
Starting Member

6 Posts

Posted - 2007-08-09 : 12:32:48

Hi tkizer

Thanks for your response.

I have excuted you script. Even though I am getting following Error.

Terminating this procedure. 'lsuper' is a forbidden value for the login name parameter in this procedure.
The number of orphaned users fixed by updating users was 0.

I saw script of SP "EXEC sp_change_users_login ".

In that script there is a validation for User_id like
user_id not in (0,1,3,4).

My user "lsuper" has the user_id value as 3 which is I'm trying to fix.

I think thats y its failing.

can you plz help me to fix this.

Thanks in advance.....

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-08-09 : 12:48:53

You should delete lsuper, then re-add it so that it doesn't have that value. I'm sure that 0,1,3,4 are system reserved ids.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

kamesh.pasupuleti
Starting Member

6 Posts

Posted - 2007-08-09 : 13:10:28

Yes I tried to DELETE that user but its not allowing me to delete the user.

Its saying you can't delete dbowner.

How can I delete that user.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-08-09 : 13:16:55

Can you change the database owner to another userid, remove db_owner permissions from the account, then delete it?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

kamesh.pasupuleti
Starting Member

6 Posts

Posted - 2007-08-09 : 13:37:51

Thank you. I will try in this way and I will get back to you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources