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
http://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

Comments (2)

  1. Anonymous says:

    The script used to identify the Guest Users in all databases works fine except when I try it on an instance that has databases with spaces or hyphens in the database name. Not sure why.

  2. Anonymous says:

    use this – same script with modification:

    –guest users

    USE master;

    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