Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Basit's SQL Server Tips

Basit Farooq is a Lead Database Administrator, Trainer and Technical Author. He has more than a decade of IT experience in development, technical training and database administration on Microsoft SQL Server platforms. Basit has authored numerous SQL Server technical articles, and developed and implemented many successful database infrastructure, data warehouse and business intelligence projects. He holds a master's degree in computer science from London Metropolitan University, and industry standard certifications from Microsoft, Sun, Cisco, Brainbench, Prosoft and APM, including MCITP Database Administrator 2008, MCITP Database Administrator 2005, MCDBA SQL Server 2000 and MCTS .NET Framework 2.0 Web Applications.

SQL Server Guest User – Still a Serious Security Threat

One of the security recommendation inside SQL Server Security Best Practice white paper for guest user is that, to disable guest access in every database (expect “master”, “msdb” and “tempdb”) on SQL Server instance, and it should not be used in any circumstances. By default, guest user exists in all user and system databases. Having guest user enabled inside databases, leaves a security risks of unauthorized (or unaudited) access to the data because Guest user allows database access to logins who do not have associated users inside SQL Server databases. By disabling guest user access from the user databases will ensure, that member of PUBLIC server role may not be able to access user databases on SQL Server instance, unless they have access to the database explicitly.

I have written following script which you can use to list all databases with guest user access enabled:

USE [master]
GO

SET NOCOUNT ON

DECLARE  @First              [smallint]
        ,@Last               [smallint]
        ,@DBName             [varchar](200)
        ,@SQLCommand         [varchar](500)
        ,@DBWithGuestAccess  [nvarchar](4000)

IF OBJECT_ID('tempdb..#GuestUsersReport') IS NOT NULL
 DROP TABLE #GuestUsersReport
CREATE TABLE #GuestUsersReport ( [Database]  [varchar](256)
        ,[UserName]  [varchar](256)
        ,[HasDbAccess] [varchar](10))

DECLARE @DatabaseList TABLE ([RowNo] [smallint] identity (1, 1), [DBName] [varchar](200))

INSERT INTO @DatabaseList
SELECT [name] FROM [master]..[sysdatabases] WITH (NOLOCK)
WHERE [name] NOT IN ('master', 'tempdb', 'msdb') ORDER BY [name]

SELECT @First = MIN([RowNo]) FROM @DatabaseList
SELECT @Last = MAX([RowNo]) FROM @DatabaseList

WHILE @First <= @Last
BEGIN
 SELECT @DBName = [DBName] FROM @DatabaseList WHERE [RowNo] = @First

 SET @SQLCommand = 'INSERT INTO #GuestUsersReport ([Database], [UserName], [HasDbAccess])' + CHAR(13)
     + 'SELECT ' + CHAR(39) + @DBName + CHAR(39) + ' ,[name], CASE [hasdbaccess] WHEN 0 THEN ''N'' WHEN 1 THEN ''Y'' END '
     + CHAR(13) +  'FROM [' + @DBName + ']..[sysusers] WHERE [name] LIKE ''guest'' AND [hasdbaccess] = 1'

 EXEC (@SQLCommand)

 SET @First = @First + 1
END

SELECT * FROM #GuestUsersReport WITH (NOLOCK)

SET NOCOUNT OFF

IF OBJECT_ID('tempdb..#GuestUsersReport') IS NOT NULL
 DROP TABLE #GuestUsersReport

Well after running the above script, if you find any databases with guest access enabled, then use the REVOKE CONNECT statement to revoke the access of guess access from user database:

--Specify database name in USE statement
USE [<SpecifyDatabaseName>]
GO

REVOKE CONNECT TO [guest]
GO

This recommendation does not apply to “master”, “msdb” and “tempdb” database. For more information, see “KB #2539091 : You should not disable the guest user in the msdb database in SQL Server” and “KB #2186935 : Guidelines on revoking Guest user access in a database“.


Comments

Leave a comment on the original post [basitaalishan.com, opens in a new window]

Loading comments...