Invite a guest in, or not?

 

The guest user in SQL Server is a bit of a funny object. While we’re taught to be polite to guests, sometimes we don’t want to invite them into our databases.

If you have a login to an instance of SQL Server that doesn’t automatically give you access to any databases…. or does it? By default guest has been “enabled” by GRANTing CONNECT to guest in master, msdb, and tempdb. While guest exists in each database, unless you enable guest in the model database guest will not be enabled by default for user databases. If you do enable guest it, like every other user, is a member of the public and has some basic permissions in that database. This is mostly around metadata.

Unfortunately this isn’t always clear in our current documents. There are some places in BOL where we say you can remove guest from any database other than master and tempdb and others where we say you can remove it from any user database. The real answer is that as of SQL Server 2008 R2 and earlier guest must be enabled in master, tempdb, AND msdb or some functionality will break. The details are documented in this new KB article: You should not disable the guest user in the msdb database in SQL Server https://support.microsoft.com/kb/2539091

The below query shows you that each database has a user called guest but you can see that most (at least by default) do not have guest enabled/CONNECT permission granted to guest. Of course sp_msforeachdb is undocumented and unsupported with all the usual caveats. You may want to look at Bob Taylor’s SQL Iterators code on Codeplex, though Codeplex is still unsupported sample code.

DECLARE @var1 varchar (1000);
SELECT @var1 = 'USE ?;SELECT ''=== ''+DB_NAME();
    SELECT name, type_desc, owning_principal_id
     FROM sys.database_principals
     WHERE name = ''guest'';
    SELECT prins.name AS grantee_name, perms.*
     FROM sys.database_permissions AS perms
     JOIN sys.database_principals AS prins
     ON perms.grantee_principal_id = prins.principal_id
     WHERE prins.name = ''guest'' AND perms.permission_name = ''CONNECT'';'
EXEC sp_msforeachdb @var1;