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 

Helpful TSQL Queries for meta search

Searching for table names 

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%prop%'

Searching for Stored Procedure and more containing text 

SELECT OBJECT_NAME(object_id)
FROM sys.sql_modules
WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1
    AND definition LIKE '%CounterpartyProperties%'
ORDER BY OBJECT_NAME(object_id)

OBJECTPROPERTY PropertyName reference list:

https://msdn.microsoft.com/en-us/library/ms176105.aspx

Batching Commands for SQL and Optimizing network round trips.

Optimizing Database Access And Diving Into .Net SqlClient by Mladen Prajdić
http://www.sqlpass.org/24hours/2016/summitpreview/Sessions/Details.aspx?sid=53738

I was watching the session and found that I would like to add to the batch commands section. Back in 2003 i wrote a query optimization model that still works to this day that i would like to share.

Problem 

You have a lot of data you need to import, pushing it one row at a time is very inefficient, but what is the "right" amount of queries to push.

Solution

SQL commands are sent through a network packet to the server, these packets are default 4096 bytes / 4KB in size, this can be defined on the system but most leave this setting intact. This means that if you write an insert for one row and sends that to the server there are plenty of unused space in the packet of 4096. 

So the task at hand is very simple, you must optimize your query so that you send as many queries in one packet of 4096 but without going over, since it then sends the overflowing query in another packet with trailing empty space as a result. 

If you watch the session by Mladen Prajdić there is a reference to a System.Data.SqlClient.SqlCommandSet that is used internally by the .Net framework, my guess is that it works similarly and tries to optimize the network traffic to the server. 

A more brutal implementation (clean and simple) is the write a batch query engine yourself and measuring the byte length of the resulting query and then only pushing it to the server when the byte length is close to 4096 but not over. 

I have shared my base code for this brutal approach on https://gist.github.com/janhebnes/726811a5ba0d36c9de7c323ae177bf22

SET STATISTICS IO ON by default in SQL Management Studio

When working with databases and indexes you should always use
"SET STATISTICS IO ON"  and
"SET STATISTICS TIME ON"



For getting the logical reads and execution times and validating the you have index coverage on your queries. But not all are aware that these can actually be set to be on by default...

The check boxes for SET STATISTICS IO and SET STATISTICS TIME are hidden in the Options > Query Execution > SQL Server > Advanced of SQL server management studio

You can find a nice summary of indexing for beginners by Kathi Kellenberger on SQL PASS
http://www.sqlpass.org/24hours/2016/summitpreview/Sessions/Details.aspx?sid=53731

Excel Losing Decimal Values When Value Pasted from SSMS ResultSet

http://blog.sqlauthority.com/2012/09/24/sql-server-excel-losing-decimal-values-when-value-pasted-from-ssms-resultset/

Remember to format cells to text in Excel, before pasting from the SQL Server Management results window.

This will keep the decimal format from SSMS as a dot and not autoformat the number and loosing the comma in the process.

T-SQL Generating af quick table with calendar dates

SELECT DATEADD(d, ones.n + 10*tens.n + 100*hundreds.n + 1000*thousands.n, GETDATE())
FROM (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) ones(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) tens(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) hundreds(n),
     (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) thousands(n)
ORDER BY 1

The fastest way to generate a base table with date entries...

Originally a method used in 2003, when we developed the statistics calculations for Skandias Fond performance dashboards.
Still available today at https://www.skandia.dk/funddatasheet?fundID=142

I keep forgetting the method, so now it is documented here.

Visual Studio Database Projects (basics / refactoring log file)

Visual Studio Database Projects 

Basics / refactoring log file

I am new to the Database projects in Visual Studio and still learning a lot of the basics.

I imported some tables from a database to my project, fiddled a little with the fields not really changing anything but saving the file... i also copied one table and renamed the fields in the file ...

When publishing i got

(50,1): SQL72014: .Net SqlClient Data Provider: Msg 50000, Level 16, State 127, Line 8 Rows were detected. The schema update is terminating because data loss might occur.
(43,0): SQL72045: Script execution error.  The executed script:
IF EXISTS (SELECT TOP 1 1
  FROM   [DSA].[Fact_Sellout_brick_totals_stg])
  RAISERROR (N'Rows were detected. The schema update is terminating because data loss might occur.', 16, 127)
    WITH NOWAIT;

The table schema Compare shows one field as changed, yet it is changed to the same value... if it was normal code it would not be marked as changed...
Looking for a Refactorlog i found .refactorlog in the root of the project and it contains the problem...

<operation Name="Rename Refactor" Key="f8eb4f84-f81d-4292-a3f3-4e285b6f5d73" ChangeDateTime="03/04/2016 08:14:48"><br /> <property Name="ElementName" Value="[DSA].[Fact_Sellout_brick_totals_stg].[Brick_code]" /><br /> <property Name="ElementType" Value="SqlSimpleColumn" /><br /> <property Name="ParentElementName" Value="[DSA].[Fact_Sellout_brick_totals_stg]" /><br /> <property Name="ParentElementType" Value="SqlTable" /><br /> <property Name="NewName" Value="MiniBrick_code" /><br /> </Operation><br />

Removing the fiddling from the refactorlog solved my publishing issues.
I have yet to find the visual studio tool way to the refactorlog...

Localization vs. Internationalization (i18n) in SQL and .NET

Internationalization is often written i18n, where 18 is the number of letters between i and n in the English word. Internationalization typically entails: Designing and developing in a way that removes barriers to localization or international deployment.

More about the term is described elegantly at w3
http://www.w3.org/International/questions/qa-i18n

----

There are many issues in handling time in systems that span globally.
One area is the database level and the other is the code level.

Prior to the introduction of the DateTimeOffset type the types offered did not let you save both the UTC time and the actual local timezone in one value. This would typically be handled by composing time upon two stored values, the datetime in UTC and the TimeZoneOffset in another field.

These shortcomings have been handled with the introduction of the DateTimeOffset value in both SQL and .NET. There is still room for improvement because we do not have the Daylight Saving Time Parameter in the type, but this is a value that is set nationally and just a pain to handle ;)

What have the larger systems been doing prior to this?

E.g.The Sitecore platform has saved all internal datetime values to the database as UTC time and let the presentation layer handle the formatting to localized time. For internal values like a publishing pipeline or an event queue having a single point of time reference is critical. This is still a relevant for system values where you want to be precise in the time comparison.

DateTimeOffset (from SQL server 2008)

http://blogs.msdn.com/b/bartd/archive/2009/03/31/the-death-of-datetime.aspx

SQL Server 2008 added a new data type named “datetimeoffset”. This is similar to the old datetime data type, with the following significant differences:

• Internally, the time is stored in unambiguous UTC format
• The local time zone offset is stored along with the UTC time, which allows the time to be displayed as a local time value (or converted to any another time zone offset)
• The data type is capable of storing more precise times than datetime

DateTimeOffset (from .NET Framework 3.5)

The same type has been introduced in .NET v3.5, it can map the new SQL type.
https://msdn.microsoft.com/en-us/library/system.datetimeoffset%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396

Choosing Between DateTime, DateTimeOffset, TimeSpan, and TimeZoneInfo
https://msdn.microsoft.com/en-us/library/bb384267%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396

The Daylight Saving Time - the missing link... 

I have found one excellent resource for handling Daylight Saving Time. The idea is to let the user select there country of origin, and save the Daylight Offset value at the same time, it can also save the gmt offset if you choose.

The right way is to find the value through the .NET framework with the TimeZone information for a region. But allowing it to be part of a custom value set by the user selection can help give a simplicity around the value. 

http://www.freeformatter.com/time-zone-list-html-select.html

SQL for Removing Non Image Blobs from a Sitecore Database

When you find yourself loading several gigabytes of database backups from production to a test environment or development chances are that most of that data is binary files in the blob table.

If these files are not image files you most likely won't need them for test or development.
So for stripping the excessive binary in your database you could opt for this Sql Script approach.

Removing Sitecore Locked Items with SQL

Locks can be a pain especially when using the PageEditor only approach, there are currently no PageEditor visualisation of the Locks on DataSources (Sitecore 6.5 rev. 111230) therefore the user will experience that the page editing is locked on the specific element with the locked data source item, and that the layout is locked for the whole page because of this.

The default Sitecore interface only has a single "my items" button under the review pane that enables a user to see his own locked items and unlock them all, there are default way to find them throughout the site on all users. 

Some pieces of the puzzle are a custom interface to enable unlocking of all locked items (http://blog.wojciech.org/?p=41) and enabling the users themselves to override the locked items (http://briancaos.wordpress.com/2010/09/10/unlock-sitecore-items/) but they do not solve the missing visualisation in the Page Editor issue, there is only a ribbon button on each user that lists all locked elements for his account. Hopefully a visualisation will be added in upcomming Sitecore releases.  In the meantime the solution is to remove the existing locks and make sure new ones don't stay fixed.

I like a direct approach to the problem, and the Sql Query for finding all Sitecore Locked Items is very simple.

SELECT [Id]
      ,[ItemId]
      ,[Language]
      ,[Version]
      ,[FieldId]
      ,[Value]
      ,[Created]
      ,[Updated]
  FROM [Sitecore_master].[dbo].[VersionedFields] 
  where Fieldid like '001DD393-96C5-490B-924A-B0F25CD9EFD8' and Value like '<r owner%'

The SQL Query for Removing the locks is also very simple...

UPDATE [Sitecore_master].[dbo].[VersionedFields] SET VALUE = '<r />' WHERE Fieldid LIKE '001DD393-96C5-490B-924A-B0F25CD9EFD8' AND VALUE LIKE '<r owner%'

Remember backing up and that fiddling in the database removes any business logic rules that might be implemented in the API layer. Its very much at your own risk.

The important settings when battling Locks are found in the web.config and for solving the issue on a long term make sure to enable AutomaticUnlockOnSaved  and disable AutomaticLockOnSave and finally remove the ability for the Lock system to block a user with RequireLockBeforeEditing set to false this way the PageEditor interface will behave as the users expect every time.

<!--  AUTOMATIC LOCK ON SAVE
If true, the a lock is automatically taken on an item
when a user saves the item.
-->
<setting name="AutomaticLockOnSave" value="false" />
<!--  AUTOMATIC UNLOCK ON SAVED
If true, the a saved item is automatically unlocked after
saving.
-->
<setting name="AutomaticUnlockOnSaved" value="true" />
<!--  REQUIRE LOCK BEFORE EDITING
If true, the user must have a lock on a document before
he can edit it, otherwise it is always ready for editing
-->
<setting name="RequireLockBeforeEditing" value="false" />

Looking at the documentation (The content_author's_cookbook) the following is stated:

Sitecore uses item locking to ensure that two different users can’t edit the same item at the same time. If two or more users somehow managed to edit the same item simultaneously only the changes that have been made by the user who pressed Save last will be available. All the other changes will be lost.
Item locking is a system whereby you lock the item you are editing and prevent other users from editing this item until you unlock it again after you have finished editing the item. 

Item locking works differently depending on the tools that you are using.

• In the Page Editor, you can lock an item before you start to edit it. 

• In the Content Editor, you must lock an item before you can edit it.

The problem could arise when we use the EditFrame for handling single fields editing, and this opens a hidden lock.