Categories
MSSQL Server

MSSQL Query Value From XML Datatype

Query some values from a XML datatype result…

SELECT [datumtijd]
      ,(CAST(body AS XML).query('declare namespace ns1="http://totietoot.nl/example/person/1/0"; 
                                 declare namespace soapenv="http://schemas.xmlsoap.org/soap/envelope/"; 
                                 declare default element namespace "http://totietoot.nl";
                                 //soapenv:Envelope/soapenv:Body/ns1:Members/ns1:Person/ns1:ID'
                               ).value('.', 'nvarchar(100)')) AS ID
      ,(CAST(body AS XML).query('declare namespace ns1="http://totietoot.nl/example/person/1/0"; 
                                 declare namespace soapenv="http://schemas.xmlsoap.org/soap/envelope/";
                                 declare default element namespace "http://totietoot.nl";
                                 //soapenv:Envelope/soapenv:Body/ns1:Members/ns1:Person/ns1:Name'
                               ).value('.', 'nvarchar(100)')) AS Name

      --,[body]
FROM [DATABASE].[dbo].[Logtable] 
WHERE [kolom1] = 'waarde' 
ORDER BY datumtijd DESC
Categories
MSSQL Server

MSSQL Test Connection String With PowerShell

Quickly test a connection string with PowerShell.

$sConString = "Data Source=localhost;Integrated Security=true;Initial Catalog=master; MultipleActiveResultSets=True;" 
$oSQLCon = new-object ("Data.SqlClient.SqlConnection") $sConString 
$oSQLCon.Open() 
$oSQLCon.Close()
Categories
MSSQL Server

MSSQL Check Effective Permissions

Check effective permissions on al securable using sys.fn_my_permissions(‘securable’, ‘class’). Class can be OBJECT, ROLE, SCHEMA, USER, etc.

EXECUTE AS LOGIN = 'DOMAIN\User'
SELECT * FROM sys.fn_my_permissions('dbo.TableName', 'Object')
REVERT

Create server or database role (QuickRef).

CREATE (SERVER) ROLE rolename
ALTER (SERVER) ROLE rolename ADD MEMBER [DOMAIN\User]

Grant or revoke permissions (QuickRef).

[GRANT|DENY|REVOKE] [SELECT|INSERT|UPDATE|DELETE|EXECUTE] (ON [dbo.TableName|SCHEMA::[SchemaName]) [TO|FROM] [Account|Role]
Example: GRANT SELECT, INSERT ON dbo.ViewName TO DOMAIN\User
Categories
MSSQL Server

MSSQL Query SQL Trace File

Query data from a SQL trace file directly.

SELECT TextData, StartTime, Duration
FROM fn_trace_gettable('X:\Path\To\TraceFile.trc', default)
WHERE EventClass = 41;

For a complete list of eventclasses you can use the query below.

SELECT * FROM sys.trace_events
Categories
MSSQL Server

MSSQL Create Update Trigger (QuickRef)

A basic update trigger. This specific trigger can be used for audit purposes.

CREATE TRIGGER dbo.TriggerDesc_UPDATE
ON dbo.TableName FOR UPDATE
AS
BEGIN
  IF UPDATE(ColumnName)
  BEGIN
    INSERT INTO dbo.AuditLogTable (IDColumn, OldValue, NewValue)
    SELECT i.IDColumn, d.Value, i.Value
    FROM inserted I
    JOIN deleted d ON i.IDColumn = d.IDColumn
  END
END