Guest user - 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

SQL Server 2005 Forums

SQL Server Administration (2005)

Guest user

Author

Topic

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2008-04-17 : 16:53:27

In SQL2K5 guest user can not be dropped from ANY databases. How ever we can use the following command to disable it.

USE <Database Name>
GO
REVOKE CONNECT FROM GUEST
GO

How to find in a DB, whether guest user is disable or not, using TSQL?

------------------------
I think, therefore I am - Rene Descartes

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-04-17 : 22:34:49

It's disabled by default. You can query sysusers and look at hasdbaccess column.

tosc
Starting Member

1 Post

Posted - 2008-04-18 : 02:25:51

Hi,

you can use this T-SQL statement for each db.

EXEC sp_MSforeachdb 'USE ;
--IF THE DATABASE IS NOT A SYSTEM DATABASE
IF DB_ID(''?'') > 4
SELECT * FROM sys.sysusers WHERE name=''guest'';'
GO

CU
tosc
http://www.insidesql.org
http://www.insidesql.org/blogs/tosc

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-04-18 : 10:02:39

How to prevent it appearing as a login in the database under security logins all together.

On my system i have a 1 in hasdbaccess

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2008-04-18 : 11:41:48

quote:
Originally posted by rmiao

It's disabled by default. You can query sysusers and look at hasdbaccess column.

Thank you, rmiao!

------------------------
I think, therefore I am - Rene Descartes

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-04-19 : 17:41:21

>> How to prevent it appearing as a login in the database under security logins all together.

It shouldn't map to any sql login, and you can't remove guest user in sql2k5.

TRACEYSQL
Aged Yak Warrior

594 Posts

Posted - 2008-04-19 : 18:53:46

When adding new database it appears in every database as a user.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-04-19 : 19:56:30

That's by design.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources