disable Guest accounts

Question

Post reply

disable Guest accounts

Aatish Patel

Ten Centuries

Points: 1040
More actions
September 11, 2017 at 8:19 am

#323493

Hi,
can someone share scripts to disable Guest account in all the databases at same time. have the below but I don't want to go thru all 50 databases one by one. Any help is appreciated
Use Database
go
REVOKE CONNECT FROM guest
GO

Viewing 5 posts - 1 through 4 (of 4 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736568 More actions · Answer 1

ms_foreachdb

https://www.mssqltips.com/sqlservertip/1414/run-same-command-on-all-sql-server-databases-without-cursors/

Sue_H SSC Guru Points: 90860 More actions · Answer 2

You want to make sure you aren't running this in master, tempdb or msdb. You will get an error if you try to disable guest in master or tempdb.
You will have issues if you disable guest in msdb. Refer to the following on some of the issues if disabled in msdb - there are other issues:
You should not disable the guest user in the msdb database in SQL Server

If you use sp_MSforeachdb, you should verfiy if it's been disabled in all databases as sp_MSforeachdb can skip databases.

Sue

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 3

Adding to what Sue_H said, and expanding on what Steve Jones said, I tend to avoid ms_foreachdb (for the reasons that Sue_H posted) and would much rather write my own cursor for it so I can ensure I am running it only where I actually want to (ie non-sytem databases).
If ms_foreachdb is the route you wish to take, I'd modify your script to also print or select the database it is using so you have a nice list of ones it was applied to.

That being said, even if I wrote my own cursor, I'd still print out something like:
PRINT 'Guest has been disabled in the database ' + @database

Just so I had some form of logging of where I had disabled it. I like having logs of what things I have changed if possible.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

Sue_H SSC Guru Points: 90860 More actions · Answer 4

Instead of writing your own cursor, you could just generate a script of the SQL Statements and avoid a cursor altogether. The statements give you a list of which databases will be affected. If it executes successfully, they were changed. And the script provides a log of what was executed. Something like:

SELECT 'USE ' + name + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) + 'REVOKE CONNECT FROM GUEST;' FROM sys.databases WHERE NAME not in ('master', 'msdb', 'tempdb')

Sue