User or role ''guest'' already exists in the current database

Question

User or role ''guest'' already exists in the current database

Mark Campbell-279903

SSC Veteran

Points: 269
More actions
January 8, 2007 at 2:57 pm

#170912

I am trying to help someone who is receiving the message User or role 'guest' already exists in the current database. This happens when a new database is created and a series of scripts are executed as part of the creation of the database. When one of the scripts runs, the statement sp_grantdbaccess guest is executed. At this time, the error occurs.
A few of us have tried to duplicate the error, but we have not. I checked the security logins on the server, but I did not see guest as a user or a role. Since this is a newly created db, it is odd the error, already exists is displayed. I have asked the person to execute profiler to trace the problem. Maybe that will help lead me to a solution.
Does anyone know what would cause this error?
Thank You!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Greg Charles SSC-Forever Points: 45403 More actions · Answer 1

The Model database probably has the guest user in it, so when a new database is created, the user is also created and exists before the scripts are run. See "Model database" and "Guest user" in BooksOnLine.

Greg

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 2

my advice is don't give any privilege to GUEST user. Remove or revoke all the privileges given to the user if there is any

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

jtango SSChasing Mays Points: 643 More actions · Answer 3

Same thoughts exactly as Sugesh previously.

Guest user allows users to access databases while their own login doesn't.

Use always domain users or - groups. Some third party installations require sql server logins, but they must be well documented and carefully used.

Revoke all database access from guest user, you don't really need it, as well as you don't need sa.

See SQL Books Online.

Happy granting

JT

a DBA

steve block SSCertifiable Points: 7698 More actions · Answer 4

I am trying to remove guest from some of my database (including model) but get an error message.

I've tried "sp_revokedbaccess 'guest'" from query analyzer and Delete User from Enterprise Manager but get the message "User 'guest' does not exist in the current database."

I did notice that the User 'guest' on the list of database users is blank under Login Name and the Database Access column has "Via group membership".

BOL says that he guest user account cannot be removed from the master and tempdb databases.

Steve

Carl Federl One Orange Chip Points: 25384 More actions · Answer 5

"Guest" is a special user and actually cannot be removed but you can revoke the database access. Once access is revoked, sp_helpuser and EM, will no longer show the user.

From the source for sp_revokedbaccess:

if lower(@name_in_db) = 'guest'

update sysusers

set status = status & ~2, updatedate = getdate()

where uid = user_id('guest')

sp_helpuser source has these restrictions on the sysusers table where 0 means false and 1 means true

WHERE islogin = 1

and isaliased = 0

and hasdbaccess = 1

and (usg.issqlrole = 1 or usg.uid is null)

P.S.

If you attempt to delete the row from the sysusers table for the guest user, you have just caused a significant problem. See http://support.microsoft.com/kb/315523/

"Removal of the guest account may cause a 916 error in SQL Server 2000 SP4 or a handled exception access violation in earlier versions of SQL Server 2000"

SQL = Scarcely Qualifies as a Language

steve block SSCertifiable Points: 7698 More actions · Answer 6

Thank you.

I have added the user guest to the roles of db_denydatareader & db_denydatawriter for all appropriate databases.

Steve