Guest user permission check for multiple databases per security best practices (SQL)
Introduction
This T-SQL sample script checks permissions for guest user in all databases. This script applies to system and to user databases.
Scenarios
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.
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.
Script
Just open a new query window using SQL Server Management Studio. Then paste this script and hit the Execute button. Sample output is below.
Here are some code snippets for your references. To get the complete script sample, please click the download button at the beginning of this page.
SQL
Edit|Remove
mysql
IF @DbName = 'msdb'
IF @CountNumPermissions = 0
BEGIN
SET @Success = 0
PRINT '*Guest user does not have (Connect) permission in database ' + @DbName + '. This is not suggested. Consider granting Connect permission in this database.'
END
IF@DbName = 'msdb'IF@CountNumPermissions = 0BEGINSET@Success = 0PRINT'*Guest user does not have (Connect) permission in database ' + @DbName + '. This is not suggested. Consider granting Connect permission in this database.'END
SQL
Edit|Remove
mysql
SET @SQLString = N'USE ' + @DbName + '; ' +
'SELECT @CountNumPermissionsOUT = COUNT(*)
FROM sys.database_permissions AS perms
JOIN sys.database_principals AS prins
ON perms.grantee_principal_id = prins.principal_id
WHERE prins.name = ''guest'' AND state_desc <> ''DENY'' '
EXEC sp_executesql
@SQLString
, N'@CountNumPermissionsOUT int OUTPUT'
, @CountNumPermissionsOUT = @CountNumPermissions OUTPUT
SET@SQLString = N'USE ' + @DbName + '; ' +
'SELECT@CountNumPermissionsOUT = COUNT(*)
FROMsys.database_permissionsASpermsJOINsys.database_principalsASprinsONperms.grantee_principal_id = prins.principal_idWHEREprins.name = ''guest'' AND state_desc <> ''DENY'' 'EXECsp_executesql@SQLString
, N'@CountNumPermissionsOUT int OUTPUT'
, @CountNumPermissionsOUT = @CountNumPermissionsOUTPUT
Additional Resources