[Script of May 17] Guest user permission check for multiple databases per security best practices

  • Article

Script Download: GuestUserCheck.zip
https://gallery.technet.microsoft.com/scriptcenter/Guest-user-permission-ac9eecdb

This T-SQL sample script checks permissions for guest user in all databases. This script applies to system and to user databases.

Knowing about guest user in Sql server is a frequent security requirement. This can also be a source of confusion since many a times guest user is disabled in Sql Server msdb's Sql server and results in issues indicated in below mentioned KB. 

Some forums for such requirements are below:
https://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/04940880-85c4-419a-a859-8a6bd58a18f0 

Such a script should also help in below mentioned KB (KB has script for single database):
https://support.microsoft.com/kb/2539091

You should not disable the guest user in the msdb database in SQL Server 

Script does below:

  • Script checks that all databases (except msdb) do not have any permission per security best practice.
  • Script checks that msdb database has Connect permission for guest user.
  • Script runs in Sql 2005/2008/2008R2/2012.

 

image image
image

You can find more All-In-One Script Framework script samples at https://aka.ms/onescriptingallery