Freigeben über

Guest user account in SQL Server

  • Artikel

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

Comments

  • Anonymous
    December 19, 2013
    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.
  • Anonymous
    March 13, 2014
    use this - same script with modification:--guest usersUSE master;GOdeclare @name sysnamedeclare @cmd varchar(4000)DECLARE databases_cursor CURSOR FORSELECT '[' + name + ']' FROM sys.databases where state in (0)order by namecreate 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 = 0BEGINset @cmd = 'use ' + @name + ';insert into #guest_usersSELECT ''' + @name + ''' as database_name, name, permission_name, state_descFROM sys.database_principals dprINNER JOIN sys.database_permissions dpeON dpr.principal_id = dpe.grantee_principal_idWHERE name = ''guest'' AND permission_name = ''CONNECT'''exec (@cmd)  FETCH NEXT FROM databases_cursor into @name;ENDselect * from #guest_users order by database_name ascdrop table #guest_usersCLOSE databases_cursor;DEALLOCATE databases_cursor;GO