SQL Server Security related scripts

  • I am looking for scripts that are used to check security related issues in Microsoft SQL Server 2008/2008R2/2012

  • You are being too vague... but... here is one to start with to see who has what type of rights.

    Select

    'Login Name'= Substring(upper(SUSER_SNAME(SID)),1,40),

    'Login Create Date'=Convert(Varchar(24),CreateDate),

    'System Admin' = Case SysAdmin

    When 1 then 'YES (VERIFY)'

    When 0 then 'NO'

    End,

    'Security Admin' = Case SecurityAdmin

    When 1 then 'YES (VERIFY)'

    When 0 then 'NO'

    End,

    'Server Admin' = Case ServerAdmin

    When 1 then 'YES (VERIFY)'

    When 0 then 'NO'

    End,

    'Setup Admin' = Case SetupAdmin

    When 1 then 'YES (VERIFY)'

    When 0 then 'NO'

    End,

    'Process Admin' = Case ProcessAdmin

    When 1 then 'YES (VERIFY)'

    When 0 then 'NO'

    End,

    'Disk Admin' = Case DiskAdmin

    When 1 then 'YES (VERIFY)'

    When 0 then 'NO'

    End,

    'Database Creator' = Case DBCreator

    When 1 then 'YES (VERIFY)'

    When 0 then 'NO'

    End

    from Master..SysLogins order by 3 Desc

  • A standard set of checks that is commonly used in some private sector environments is the IASE STIGs.

    They used to have version for SQL Server 2005 and 2008 on the site but since they have upgraded the documents to SQL Server 2012 they ended up removing older versions.

    The checklist are divided up by installation and then database each with their own checks for each. I used these constantly as a DBA contractor with DISA. I have not gone through all of the current version. You can also find checklist for Operating System versions as well on this site.

    Shawn Melton
    Twitter: @wsmelton
    Blog: wsmelton.github.com
    Github: wsmelton

  • Many Thanks. That is what I was looking exactly. Do you know any script that lists all users with all their permissions in all databases on a particular server/instance.

  • Please refer to the following article to get the script to get logins, databases users/roles and object level permission (T-SQL):-):

    https://gallery.technet.microsoft.com/scriptcenter/Get-logins-databases-816f66b2

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply