Fix for orphan user 'guest'?

Question

What can be done, if anything, when the special guest user is orphaned (not linked to any login)?

For one of my databases (SQL Server 2005), executing the following lists the guest user as an orphaned user.

exec sp_change_users_login 'report'

Results:

UserName    UserSID
guest       0x3C2E66759FFBC14F84127D6795C27FD3

If I try to fix the guest user using that procedure, I get the following:

exec sp_change_users_login 'update_one', 'guest', 'guest'

Terminating this procedure. 'guest' is a forbidden value for the login name parameter in this procedure.

If I try to delete the user, I get:

User 'guest' cannot be dropped, it can only be disabled.

select * from sys.database_principals where name = 'guest'

Results in:

name                 guest
principal_id         2
type                 S
type_desc            SQL_USER
default_schema_name  guest
create_date          11/13/98 2:58 AM
modify_date          10/16/01 4:31 PM
owning_principal_id  NULL
sid                  0x3C2E66759FFBC14F84127D6795C27FD3
is_fixed_role        0

The database seems to be confused as to whether this is a special user or not. Is there anything that can be done?

Answer 1

The user "guest" is never assigned to a server login, even on a fresh install it is classified as a SQL user without a login. As you can only set the SID of a login (upon creation), and not a user I don't believe that this is possible; sp_change_users_login does not work precisely for the reason that the guest account should never be mapped to a server login. As a result, the user "guest" is always an orphaned user. Probably not the answer you wanted though :)

Answer 2

My thoughts... The reason sp_change_users_login is throwing that error is because MS wrote it too. [Looking through system procedure code every now and then can be fun. :)] However the fact that it shows up when running the report is indicating that someone/some process messed with the account, or a possible hot fix from MS could have done it (you never know).

The guest account is supposed to be there, it exist in every database that is created since the account exist in model by default. Making it not show up in the report would probably require getting that SID changed back to 0x00 I would guess. As long as the account is disabled I would leave it alone and ignore it. If it was really bothering me though I would fork out the money and put a call in with Microsoft support.

Answer 3

Note : Whenever you move your database from one server to another server there is a problem of orphan user usually occur. FileListOnly is the new term in sql server that has all the details of the created backup that who has access to it.

So there are sequence of task that you have to follow

1. Firstly you have to restore the FileListOnly from the created backup to the destination or new server.
2. Restore the backup.
3. Use the sp_change_users_login as per the need. For help regarding this procedure you can refer http://msdn.microsoft.com/en-us/library/ms174378.aspx.

I put a example here hope it will help:

> RESTORE FILELISTONLY FROM DISK = N'C:\YourDB.bak'
> 
> RESTORE DATABASE YourDb FROM DISK = N'C:\YourDB.bak' WITH MOVE
> N'YourDB' TO N'D:\YourDB.mdf', MOVE N'YourDB_log' TO N'D:\YourDB.ldf',
> REPLACE
> 
> exec YourDB.dbo.sp_change_users_login 'update_one', 'UserName','UserName'