You are here:Home » tsql » Disable Guest User in all databases of SQL Server

Disable Guest User in all databases of SQL Server

I was surprised when i was working with user access on the databases of SQL Server. I have disabled the windows authentication from instance, even all people were able to access all the databases through windows authentication that was my surprised. After that i found that guest user is enable and had access to all the databases on that instance.

I have revoked CONNECT access from guest user from all the database by script. After i was unable to login with windows authentication. Below are the script for the same

DECLARE @SQL VARCHAR(100)
DECLARE @DBNAME VARCHAR(100)

-- Declaring cursor
DECLARE cur_db CURSOR READONLY 
FOR
SELECT NAME FROM SYS.SYSDATABASES
where NAME not in
('master','tempdb','msdb')

-- Opening cursor
OPEN cur_db

-- Getting values from cursor
FETCH NEXT FROM cur_db INTO @DBNAME

WHILE @@FETCH_STATUS = 0
BEGIN

SET @SQL = '
USE '+ @DBNAME + ';
REVOKE CONNECT FROM GUEST;'

--Executing statement
PRINT (@SQL)
EXEC (@SQL)

FETCH NEXT FROM cur_db INTO @DBNAME
END

--Closing and deallocating cursor
CLOSE cur_db
DEALLOCATE cur_db