How to remove the 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

General SQL Server Forums

New to SQL Server Administration

How to remove the guest user

Author

Topic

lorellana
Starting Member

25 Posts

Posted - 2011-09-22 : 17:28:41

How can I remove the guest user from all databases?

I executed:
USE model
GO
REVOKE CONNECT FROM GUEST
GO
and, so on by each database.

and then
EXEC sp_MSforeachdb 'USE

;
SELECT * FROM sysusers;'
GO

But, continue showing user: guest

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2011-11-16 : 05:31:05

Refer this article before removing the guest user
http://www.mssqltips.com/sqlservertip/1172/sql-server-database-guest-user-account/

Madhivanan

Failing to plan is Planning to fail

Sachin.Nand

2937 Posts

Posted - 2011-11-16 : 06:10:40

Is this not working ?


EXEC sp_MSforeachdb 'USE ?;REVOKE CONNECT FROM GUEST;'
GO

PBUH

vikki.seth
Yak Posting Veteran

66 Posts

Posted - 2011-11-19 : 02:05:45

If you run the above command, you will get below error.

Msg 15182, Level 16, State 1, Line 1
Cannot disable access to the guest user in master or tempdb.
So you have to exlude the master and tempdb .

This query may help you disable the guest user from all other database excluding master and tempdb.

exec master..sp_MSForeachdb
'
-- exit if master and database
if ''?'' in (''master'',''tempdb'') return
use ?
REVOKE CONNECT FROM GUEST;
print ''Guest user disabled on ''+db_name()

Sachin.Nand

2937 Posts

Posted - 2011-11-19 : 03:26:58

Yes it will error for these 2 db's but will remove the connect for the other db's.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources