Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Its SID is listed as 0x3C2E66759FFBC14F84127D6795C27FD3 instead of 0x00 –  JustinStolle Jun 4 '12 at 21:52
    
Have you tried to do the auto fix yet? I'm curious to see the result. –  SQLRockstar Jun 4 '12 at 21:58
    
I get Terminating this procedure. 'guest' is a forbidden value for the login name parameter in this procedure. –  JustinStolle Jun 4 '12 at 22:03
    
You get that error because you can't have guest as the user name (msdn.microsoft.com/en-us/library/ms174378.aspx). I am having a hard time recreating your scenario. –  Thomas Stringer Jun 4 '12 at 23:27
    
I understand that. I'm not sure how guest happened to get into this state, but noticed it in doing some db maintenance. It may not be important enough to worry about, but would be curious to know if there's an answer. –  JustinStolle Jun 5 '12 at 0:00

3 Answers 3

up vote 5 down vote accepted
+50

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 :)

share|improve this answer
    
Do you think there is a way to backup/restore this database to another location while leaving out the guest user from the restore? –  JustinStolle Aug 17 '12 at 16:43
    
Yes but it's not pretty. A simple backup and restore would not work as the guest user would be included in that. You would need to start with a fresh database created using the same method you used to install it originally (assuming the guest in model has the correct SID). Then from your existing system export all the data into your new database. This would work, but it is a lot of effort for something so minor... –  Mr.Brownstone Aug 17 '12 at 18:09
    
So despite the account having an unexpected SID, you think there isn't really a problem with it and it's safe to ignore it in the sp_change_users_login report? –  JustinStolle Aug 20 '12 at 17:39
    
In this instance I would say so, based on the fact that there isn't really that much you can actually do about it anyway. Then there's the classic debate about whether you should/shouldn't be using it anyway, but that's a different question entirely... :) –  Mr.Brownstone Aug 20 '12 at 19:03

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.

share|improve this answer
    
In other words, you are saying it isn't a problem that guest shows up in the results in this case. –  JustinStolle Aug 20 '12 at 17:36
    
Pretty much, yes. –  Shawn Melton Aug 20 '12 at 19:59

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'
share|improve this answer
    
This won't work for the guest user. See answer above by Mr.Brownstone. –  Simon Righarts Aug 16 '12 at 20:41

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.