Guest user account in SQL Server

It is recommended to disable guest user in every database as a best practice for securing the SQL Server. Guest user permits access to a database for any logins that are not mapped to a specific database user. The guest user cannot be dropped but it can be disabled by revoking the CONNECT permission. The recommendation is not valid for master, msdb and tempdb system databases. If Guest user is disabled in msdb system database, it may cause some issues. Distribution database is also system database and more information about the Guest User in distribution database can be found below.

How to revoke CONNECT permission from the Guest user in a database

 use [AdventureWorks2008]
go
REVOKE CONNECT FROM GUEST

How to grant CONNECT permission to the Guest user in a database

 use [AdventureWorks2008]
go
GRANT CONNECT TO GUEST
 Replication Agent Security Model
 https://msdn.microsoft.com/en-us/library/ms151868.aspx

Merge Agent for a pull subscription

The Windows account under which the agent runs is used when it makes connections to the Subscriber. This account must at minimum be a member of the db_owner fixed database role in the subscription database.

The account that is used to connect to the Publisher and Distributor must:

  • Be a member of the PAL.
  • Be a login associated with a user in the publication database.
  • Be a login associated with a user in the distribution database. The user can be the Guest user.
  • Have read permissions on the snapshot share.

The script below can be used to identify the Guest Users in all databases

 --guest users
USE master;
GO
if not exists (select name from sys.databases where name = 'SQLAdmin')
    create database SQLAdmin
go
declare @name sysname 
declare @cmd varchar(4000)
DECLARE databases_cursor CURSOR FOR
SELECT name FROM sys.databases where state in (0)
order by name

create table #guest_users (
    database_name sysname, principal_name sysname, permission_name nvarchar(128), state_desc nvarchar(6)
)

OPEN databases_cursor;

FETCH NEXT FROM databases_cursor into @name;

WHILE @@FETCH_STATUS = 0
BEGIN
set @cmd = 'use ' + @name + '; 
insert into #guest_users
SELECT ''' + @name + ''' as database_name, name, permission_name, state_desc
 FROM sys.database_principals dpr
 INNER JOIN sys.database_permissions dpe
 ON dpr.principal_id = dpe.grantee_principal_id
 WHERE name = ''guest'' AND permission_name = ''CONNECT''
' 
exec (@cmd)
   FETCH NEXT FROM databases_cursor into @name;
END

select * from #guest_users order by database_name asc

drop table #guest_users

CLOSE databases_cursor;
DEALLOCATE databases_cursor;
GO