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;

Leave a Reply

Your email address will not be published.