sql - ASP.NET MVC Multi tenant Application using Single DB Multiple Schema : Calling Store Procedures from the application tries to access dbo tables -

-

i developing multi tenant application using asp.net mvc, sql server, dapper single db , multiple schema each tenant. tenant assigned db user owns tenant schema. have set of tables in dbo , set shared schema.

say have dbo.tenant , anyschema.table1. application connection string set anyschema user, if call select * table1 returns values anyschema.table1. if have same query in store procedure, throws error tries access dbo.table1.

i have provided execute access dbo tenant db user single sp being shared tenant @ dbo.sp_name

how can execute store procedure logged in tenant db user, accessing table1 of anyschema single sp can used tenants.

assume have 2 users in our database tenant1 , tenant2 own schemas (also called tenant1 , tenant2).

we create contacts table each tenant have tenant1.contacts , tenant2.contacts.

within our application execute following sql using dapper. using tenant specific connection string uses tenant's sql server login details.

select * contacts

since did not specify schema within sql inferred default schema of user under sql executed. tenant1 query tenant1.contacts , tenant2 query tenant2.contacts.

now we'll create stored procedure in dbo schema executes same query:

create procedure getcontacts begin     select * contacts end go

note i've not specified schema in stored procedure's sql.

when execute stored procedure our application schema of stored procedure can still inferred current user but sql within sp infers schema of sp. if sp belongs dbo schema execute query against dbo.contacts table.

this happening in situation. have created stored procedure in dbo schema , expecting execute sql against tenant's default schema.

you have few options

  1. pass schema stored procedure parameter , use dynamic sql - how pass schema parameter stored procedure in sql server?
  2. create version of sp each tenant e.g. tenant1.sp_name, tenant2.sp_name
  3. don't use stored procedures.

Comments

Post a Comment

Popular posts from this blog

python - Subclassed QStyledItemDelegate ignores Stylesheet -

-
i'm trying subclass qstyleditemdelegate remove focus rectangle in qcombobox . even though i'm calling base implementation of paint function , nothing else, result different. looks if parts of style-sheet influence bounding box of item taken consideration. class pstyleditemdelegate(qstyleditemdelegate): def __init__(self, *args, **kwds): super(pstyleditemdelegate, self).__init__(*args, **kwds) def paint(self, *args, **kwargs): qstyleditemdelegate.paint(*args, **kwargs) what have paint unmodified qstyleditemdelegate ? as suggested tried replacing pyside pyqt4 , works, appears bug. update pyside 1.1.2 1.2.1 result same. unfortunately switch breaks other parts of code, if there no other suggestions i'm going accept answer. edit bug tracked here
Read more

node.js - StackOverflow API not returning JSON -

-
i using node.js script gather data stackoverflow. know responses gzipped, put code in should take care of that. script follows: var request = require('request'); var zlib = require('zlib'); function getstackoverflowresponse(url, callback){ request(url, {encoding: null}, function(err, response, body){ if(response.headers['content-encoding'] == 'gzip'){ zlib.gunzip(body, function(err, dezipped) { callback(dezipped); }); } else { callback(body); } }); } var url = "https://api.stackexchange.com/docs/questions#pagesize=2&order=desc&min=2014-01-04&max=2014-02-02&sort=activity&tagged=apigee&filter=default&site=stackoverflow&run=true"; getstackoverflowresponse(url, function(questions) { console.log(questions); }); instead of getting json output, i'm getting following response: buffer 0d 0a 0d 0a 0d 0a 0d 0a 3c 21 44 4f 43 54 59 50 45 20 48...
Read more

java - HttpClient 3.1 Connection pooling vs HttpClient 4.3.2 -

-
i have used httpclient3.1(java) connection pooling below : import org.apache.commons.httpclient.httpclient; import org.apache.commons.httpclient.multithreadedhttpconnectionmanager; import org.apache.commons.httpclient.params.httpclientparams; import org.apache.commons.httpclient.params.httpmethodparams; import org.apache.log4j.logger; public class httpclientmanager { private static logger logger = logger.getlogger(httpclientmanager.class); private static multithreadedhttpconnectionmanager connectionmanager = new multithreadedhttpconnectionmanager(); private static httpclientparams clientparams = new httpclientparams(); static { try { clientparams.makelenient(); clientparams.setauthenticationpreemptive(false); clientparams.setparameter(httpmethodparams.so_timeout, new integer(30000)); // set so_timeout clientparams.setparameter(httpmethodparams.head_body_check_timeout, new integer(3000)); // head responses ...
Read more