SQL Security Auditting

For allowing more general auditing between database environments you could find the following is useful for dumping logins, users, roles and their releated object permissions into a comparable format.

CREATE SCHEMA [audit] AUTHORIZATION [db_owner]

go

-- =============================================
-- Description: Audit Serverlevel logins and roles
-- =============================================
CREATE PROCEDURE [audit].Serverlevel_logins_and_roles
AS
  BEGIN
      --Server level Logins and roles
      SELECT sp.NAME                  AS LoginName,
             sp.type_desc             AS LoginType,
             sp.default_database_name AS DefaultDBName,
             slog.sysadmin            AS SysAdmin,
             slog.securityadmin       AS SecurityAdmin,
             slog.serveradmin         AS ServerAdmin,
             slog.setupadmin          AS SetupAdmin,
             slog.processadmin        AS ProcessAdmin,
             slog.diskadmin           AS DiskAdmin,
             slog.dbcreator           AS DBCreator,
             slog.bulkadmin           AS BulkAdmin
      FROM   sys.server_principals sp
             JOIN master..syslogins slog
               ON sp.sid = slog.sid
      WHERE  sp.type <> 'R'
             AND sp.NAME NOT LIKE '##%'
  END

go

-- =============================================
-- Description: Audit Databaselevel users and roles
-- =============================================
CREATE PROCEDURE [audit].Databaselevel_users_and_roles
AS
  BEGIN
      DECLARE @SQLStatement VARCHAR(4000)
      DECLARE @T_DBuser TABLE
        (
           dbname           SYSNAME,
           username         SYSNAME,
           associateddbrole NVARCHAR(256)
        )

      SET @SQLStatement=
' SELECT ''?'' AS DBName,dp.name AS UserName,USER_NAME(drm.role_principal_id) AS AssociatedDBRole  FROM ?.sys.database_principals dp LEFT OUTER JOIN ?.sys.database_role_members drm ON dp.principal_id=drm.member_principal_id  WHERE dp.sid NOT IN (0x01) AND dp.sid IS NOT NULL AND dp.type NOT IN (''C'') AND dp.is_fixed_role <> 1 AND dp.name NOT LIKE ''##%'' AND ''?'' NOT IN (''master'',''msdb'',''model'',''tempdb'') ORDER BY DBName'

    INSERT @T_DBuser
    EXEC Sp_msforeachdb
      @SQLStatement

    SELECT dbname,
           username,
           associateddbrole
    FROM   @T_DBuser
    ORDER  BY dbname,
              username,
              associateddbrole
END

go

-- =============================================
-- Description: Audit Objectlevel permissions
-- =============================================
CREATE PROCEDURE [audit].Objectlevel_permissions
AS
  BEGIN
      DECLARE @Obj VARCHAR(4000)
      DECLARE @T_Obj TABLE
        (
           dbname     SYSNAME,
           username   SYSNAME,
           issqlrole  BIT,
           objectname SYSNAME,
           permission NVARCHAR(128)
        )

      SET @Obj='USE [?]; SELECT ''?'' AS DBName, Us.name AS username, Us.issqlrole AS issqlrole, Obj.name AS object, dp.permission_name AS permission  FROM sys.database_permissions dp JOIN sys.sysusers Us  ON dp.grantee_principal_id = Us.uid  JOIN sys.sysobjects Obj ON dp.major_id = Obj.id '

      -- for each database can be done with USE [?];
      INSERT @T_Obj
      EXEC Sp_msforeachdb
        @Obj

      --SELECT UserName, issqlrole, DBName, ObjectName, Permission FROM @T_Obj ORDER BY UserName, issqlrole, DBName, ObjectName, Permission
      SELECT dbname,
             objectname,
             permission,
             username,
             issqlrole
      FROM   @T_Obj
      WHERE  dbname NOT IN ( 'master', 'msdb' )
      ORDER  BY dbname,
                objectname,
                permission,
                username,
                issqlrole
  END

go 

C# Sending secure e-mails that are signed and encrypted

Securing email content can be done by sending the email in a closed network setup, directly to the receiving email server or by encrypting the content of the email. If you require the content to be really confidential you should go all the way and authenticate the sender by signing the email with a private certificate from the sender and encrypt the same email with the public certificate of the receiver.

This opens up a couple of failure points since certificates expire.
The following Gist handles both the signing and the encrypting but also takes care of notifying when the certificates have or are going to expire, and handles when they are expired.

Using a NAS for storing your Windows Domain Users folders

At 1508 we have used a NAS (Synology DS1010+) for handling the users private folders.

The users folder are part of the users private folders and requires that each of the folders are locked down to the specific user so their data is secure. Managing the NTFS ACL security can either be handled by a manuel process or automated using a tool like SetACL by Helge Klein (http://helgeklein.com/) and a shell batch.

We have chosen to utilities a script for security a strict implementation and an easy way of resetting security on all folders in the root.

We created two jobs for managing the ACL's Access Control List, the files are located in the root of the users share on the NAS with the SetACL.exe in the same location.

SetPermission (edit me).cmd

The job can create a single folder and set the permissions, this is used for creating a new user in the domain. 

set userfolder=lpd
rem set security and clear custom settings below
"\\nas\users\SetACL.exe" -on "\\nas1\users\%userfolder%" -ot file -actn ace -ace "n:1508DOMAIN\%userfolder%;p:change" -ace "n:Domain Admins;p:full" -rec cont_obj -actn clear -clr "dacl"
rem remove inherited security
"\\nas\users\SetACL.exe" -on "\\nas1\users\%userfolder%" -ot file -actn setprot -op "dacl:p_c" -rec cont_obj
pause

ResetPermissions.cmd

For allowing the folder structure to be reset we created a batch that used the above line and detected the username based on the foldername.

@echo off

if "%~1"=="?" goto HELP
if "%~1"=="/?" goto HELP
set target=%~dp0%

echo Target must be an attached drive: %target%
FOR /F %%f IN ('DIR %target% /B /AD') DO CALL :executeResetACL %%f
echo Done!
pause
GOTO:EOF

:executeResetACL
rem "%target%SetACL.exe" -on "%target%%1" -ot file -actn setowner -ownr "n:1508DOMAIN\jhe" -rec cont_obj -actn clear -clr "dacl,sacl"
"%target%SetACL.exe" -on "%target%%1" -ot file -actn ace -ace "n:1508DOMAIN\%1;p:change" -ace "n:Domain Admins;p:full" -rec cont_obj -actn clear -clr "dacl"
GOTO:EOF

:HELP
echo  Set ACL change access to the Network Service on the specified path
echo.   
GOTO:EOF

These files have helped us run the domains private user shares for 5 years now...

Feel free to use the scripts as you like.