Installing and configuring an SAS OLEDB-driver for MSSQL

To get the correct SAS OLEDB driver or newest SAS OLEDB driver. It is best to do a search e.g. on Google.
NB! You need an account at SAS to be able to download.

And even thou this guide for Installing and configuring an SAS OLEDB-driver for MSSQL is old – it is still very useful.

Using MSSQL TEMPDB in SAS

You can use the TEMPDB in Microsoft SQL-server through SAS by creating a ODBC-libname – like the libname below.

libname TMPLIB ODBC NOPROMPT="DRIVER=SQL Server; SERVER=<SERVERNAME>; DATABASE=TEMPDB; TRUSTED_CONNECTION=yes" schema=DBO CONNECTION=SHARED;

It’s important to provide the option CONNECTION=SHARED or else it will not work.

Through the libname it’s now possible to write and read from TEMPDB. The dataset has to have this syntax ‘#<DATASETNAME>’n e.g. like below ‘#temp’n

data tmplib.'#temp'n;
set sashelp.class;
run;

Be aware, that you are not able to view this new table through the Display Manager in SAS. When Microsoft SQL-server names the table, it makes the table name longer than SAS is able to display in the Display Manager.
You’re able to verify that the table do exist through SSMS (SQL Server Management Studio), or you can verify it’s existence by reading it back to SAS by using the code below.

proc sql;
create table temp as
select *
from tmplib.'#temp'n
;
quit;

If you want to use MSSQL-server temp-tables in Pass-Through SQL in SAS, then you need to use the libname option dbmstemp=yes.
Using this option will make it possible to execute the code below and force SAS to use the MSSQL-server to process the SQL-code in the pass-through SQL. If the option dbmstemp=yes is not used, then SAS will pull the data from the MSSQL-server back to be executed locally on the SAS-installation. It works with the below driver.

libname tmplib odbc noprompt="driver=odbc driver 11 for sql server; server=<servername>; database=tempdb; trusted_connection=yes" schema=dbo connection=shared dbmstemp=yes;

data tmplib.<SASTEMP-tablename>;
set <tablename>;
run;

proc sql noprint;
connect to odbc (noprompt="driver=odbc driver 11 for sql server; server=<servername>; trusted_connection=yes");
create table <tablename> as
select * from connection to odbc
(
select a.*
from <tablename> a
inner join tempdb.##<SASTEMP-tablename> b on a.<variable> = b.<variable>
);
disconnect from odbc;
quit;

Further information can be found in this link: http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/
default/viewer.htm#a002677192.htm

 

Add trigger to all tables in schema

The code below will add a trigger to all the tables in a specific schema on the Microsoft
SQL-server.

-- Uses the build-in stored procdure in Microsoft SQL-server to run through all tables.
EXEC sp_MSForEachTable 
-- Initial command for the DB to use.
@precommand = 'use <db>',
-- Check if trigger already exists on table. If it does drop/delete it.
@command1 = 
'
IF OBJECT_ID(''[user].[?_trig]'', ''TR'') IS NOT NULL
BEGIN
	DROP TRIGGER [user].[?_trig]
END
',
-- Create trigger on all tables in a specific schema.
@command2 = '
CREATE TRIGGER [?_trig]
ON ?
AFTER INSERT
AS
	SET NOCOUNT ON
	INSERT [<schema>].[<table>] ([<column1>], [table_name], [dtNow])
	SELECT <columnname>, ''?'', CURRENT_TIMESTAMP FROM ?
GO
',
-- Only look at tables in a specific schema.
@whereand = 'and upper(schema_name(schema_id)) = ''<SCHEMA>'''
go

 

Getting accessible schemas for a user

The code below gets the schemas from a Microsoft SQL-server that is accessible for a given user. It is made with SAS-code but the SQL-statement can be used regardless of using SAS.

proc sql noprint;
connect to odbc(datasrc=&_datasrc user=Your user; pwd="Your password");
select schema_name into :_schema_names separated by " "
from connection to odbc
(
select s.name as schema_name
from sys.schemas as s
inner join
sys.database_permissions as dbp
on s.schema_id=dbp.major_id
inner join
sys.sysusers as u
on
u.uid=dbp.Grantee_principal_id
where dbp.Class_desc='SCHEMA' and lower(u.name) = %str(%')Your domain\&uid.%str(%')
);

%put &_schema_names;
disconnect from odbc;
quit;

Find partitioned tables in Microsoft SQL-server

This will find partitioned tables

select distinct object_name(object_id) from sys.partitions
group by object_id, index_id
having COUNT(*) > 1

This will find the partitions for a specific table

SELECT *
FROM sys.partitions
WHERE OBJECT_ID = OBJECT_ID(‘<Schema>.<Table>’)

Securing the Tabular BI Semantic Model

Summary: This paper introduces the security model for tabular BI semantic models in SQL Server 2012. You will learn how to create roles, implement dynamic security, configure impersonation settings, manage roles, and choose a method for connecting to models that works in your network security context.

Download here