SQL Server – Security – Privileging & Tracking the Guest Account

Background

Each month we get databases from our Vendors.  And, we have to review data in those tables.

There are a couple of pathways to quickly granting accessing to our users.

In this post, we will grant access to the guest user.

 

SQL

Grant Access



grant connect to guest;

Review Access

Review Permissions grant/denied to Guest on Contextual Database

Code


declare @principal sysname

set @principal = 'guest'

; with cteObject
(
      objectID
    , [object]
    , schemaID
    , [schema]
)
as
(
    select
              tblSO.object_id
            , tblSO.[name]
            , tblSO.schema_id
            , tblSS.[name]

    from   sys.objects tblSO

    inner join sys.schemas tblSS

            on tblSO.[schema_id] = tblSS.[schema_id]
)
select 

          tblSP.[name]
        
        --, tblSDP.*

        , tblSDP.[state_desc]

        , tblSDP.[permission_name]

        , [objectClass]
            = tblSDP.class_desc

        , [object]
            = coalesce
                (
                     quoteName
                        (
                            cteO.[schema]
                        )
                    + '.'
                    + quoteName
                        (
                            cteO.[object]
                        )

                    , null
                )

            , [grantee]
                = SUSER_NAME(tblSDP.grantor_principal_id)

from   sys.database_principals tblSP

inner join sys.database_permissions tblSDP

    on tblSP.principal_id = tblSDP.[grantee_principal_id]

left outer join cteObject cteO

    on tblSDP.major_id = cteO.objectID

where tblSP.[name] = @principal

order by

    tblSP.[name] asc


Review Access for contextual database

Code

use master
go

set nocount on
go

set XACT_ABORT on
go

declare @tblResult table
(
      [database]    sysname
    , [principal]   sysname
    , [access]      sysname
    , [grantee]     sysname null
)

declare @command nvarchar(4000)

set @command =
                'if databasepropertyex(''?'', ''Collation'') is not null '
                + ' begin '
                + ' use [?]; '
                + ' select [database] = db_name(), tblSP.name,  tblSDP.[state_desc], SUSER_NAME(tblSDP.grantor_principal_id) '
                + ' from sys.database_principals tblSP '
                + ' inner join sys.database_permissions tblSDP '
                + ' on tblSP.principal_id = tblSDP.grantee_principal_id '
                + ' where tblSP.[name] = ''guest'' '
                + ' and tblSDP.[permission_name] = ''CONNECT'' '
                + ' and tblSDP.[state_desc]  = ''GRANT'' '
                + ' end '

insert @tblResult
(
      [database]    
    , [principal]   
    , [access]      
    , [grantee] 
)
exec master.dbo.[sp_MSforeachdb]
        @command1 = @command

select *

from   @tblResult tblR

order by 
    tblR.[database]

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s