1

I wrote this simplistic script (I'm not very familiar with TSQL and dynamic SQL) to revoke all connections from guest accounts:

DECLARE @database_id int, @database_name nvarchar(100);

DECLARE database_cursor CURSOR FOR
SELECT name
FROM [master].sys.databases
WHERE name NOT IN ('master', 'tempdb')
AND state = 0

OPEN database_cursor

FETCH NEXT FROM database_cursor
INTO @database_name

while (@@FETCH_STATUS <> -1)
        BEGIN
                Print @database_name
                exec('USE [' + @database_name + ']')

REVOKE CONNECT FROM 'guest'

                FETCH NEXT FROM database_cursor
                INTO @database_name
        END

CLOSE database_cursor
DEALLOCATE database_cursor

But apparently the REVOKE was still being applied to the master database.

So then I modified the script to this:

DECLARE @database_id int, @database_name nvarchar(100);

DECLARE database_cursor CURSOR FOR
SELECT name
FROM [master].sys.databases
WHERE name NOT IN ('master', 'tempdb')
AND state = 0

OPEN database_cursor

FETCH NEXT FROM database_cursor
INTO @database_name

while (@@FETCH_STATUS <> -1)
        BEGIN
                Print @database_name
                EXEC('USE [' + @database_name + '];'+

'REVOKE CONNECT FROM GUEST;'

                    );

                FETCH NEXT FROM database_cursor
                INTO @database_name
        END

CLOSE database_cursor
DEALLOCATE database_cursor

The script runs, but the changes aren't be saved for some reason because the guest account is not revoked when I check for it afterwards.

Sorry, if I'm missing any other details. I'm so unfamiliar to this realm, I'm not even sure how to frame the question.

Thanks in advance!

Update 1: I'm checking that it's been revoked using this script from the DISA STIGs:

SELECT COUNT(grantee_principal_id)
FROM sys.database_permissions
WHERE grantee_principal_id = 2
AND state = 'G'
AND permission_name = 'CONNECT'
  • How are you checking that it was revoked? The only evidence you would see is that there is no CONNECT entry in databasename.sys.database_permissions for guest. – Kenneth Fisher Apr 21 '14 at 18:32
  • @KennethFisher: Hi Kenneth, I've added part of the script I'm using to check that the change has been applied. Is that doing what you're saying?: "there is no CONNECT entry in databasename.sys.database_permissions for guest". Thank you for your help! :) – Zhao Li Apr 21 '14 at 21:04
  • The only thing I see of the top of my head is that I'm not certain that the principal ID for guest is always a 2. Here is some light reading for you though: blog.sqlauthority.com/2012/02/24/… and blog.sqlauthority.com/2012/02/20/… – Kenneth Fisher Apr 21 '14 at 21:47
  • FYI I tried your script myself on a test box and it worked correctly. That's why I'm guessing it is your conformation script. – Kenneth Fisher Apr 22 '14 at 3:36
  • @KennethFisher I just relooked at the confirmation script because of your help and you are correct. I made the same mistake as the first error I posted above. I was inspecting master and not the individual databases. Thanks for your help! If you post an answer, then I can accept it. Thank you so, so much for guiding me to my noob mistake. :) – Zhao Li Apr 23 '14 at 19:17
1

I ran your script with no problems so my guess is that something is wrong with your conformation script. I don't see anything specifically wrong with it but here are a couple of links with additional information about disabling guest.

http://blog.sqlauthority.com/2012/02/24/sql-server-guest-user-and-msdb-database-enable-guest-user-on-msdb-database/

http://blog.sqlauthority.com/2012/02/20/sql-server-disable-guest-account-serious-security-issue/

Here is a a modified version of Pinal Dave's script that should help you confirm.

EXEC sp_msforeachdb 'USE [?];
    SELECT ''?'' DBName, name, permission_name, state_desc
    FROM sys.database_principals prin
    INNER JOIN sys.database_permissions perm
        ON prin.principal_id = perm.grantee_principal_id
    WHERE name = ''guest'' AND permission_name = ''CONNECT''';
| improve this answer | |
0

From what I read, disabling guest account in master, msdb and tempdb is not advisable. For references and additional information; please read the below linked articles.

From SQL*Server BOL

  • The guest user cannot be dropped, but guest user can be disabled by revoking its CONNECT permission by executing REVOKE CONNECT FROM GUEST within any database other than master or tempdb.

And also from the following KB article

  • SQL Server Books Online recommends that you disable the guest user in every database as a best practice for securing the database server. This recommendation does not apply to master, msdb, and tempb system databases. In order for some Microsoft SQL Server features to work, the guest user must be enabled in the msdb database. This article describes some issues that you may experience if you disable the guest user in the msdb database. The article also provides information about how to resolve those issues.
| improve this answer | |
  • I guess you meant to say that the OP needs to exclude msdb from the list of databases on which the script operates, in addition to master and tempdb that are already being excluded. – mustaccio Apr 21 '14 at 19:22
  • @sqlchow: Thanks for the input. And yes, I'm trying to apply this change on databases other than master and tempdb. I can add msdb to the list also, but that's not helping me verify that the changes have or have not been applied. Thank you for your time! :) – Zhao Li Apr 21 '14 at 21:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.