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
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