MSSQL Server

All posts containing MSSQL-related scripts and patterns.

MSSQL Query Value From XML Datatype

/ MSSQL Server /

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

MSSQL Query Value From XML Datatype Read More »

MSSQL Check Effective Permissions

/ MSSQL Server /

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]

>GRANT SELECT, INSERT ON [dbo].[ViewName] TO DOMAIN\User
>GRANT EXECUTE ON [dbo].[Procedure] TO [RoleName]

MSSQL Check Effective Permissions Read More »