July 19, 2022 at 9:26 am
why its geting data from dbo table but not from user1?
July 19, 2022 at 9:53 am
So are these objects in the 2 different schemas identical? I suppose the question then it why do you have the same objects in 2 different schemas? Sounds like you should have 1 table, and then have a column to denote what your Schema is being used to identify. If security is a concern, then you could make use of RLS.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 19, 2022 at 3:19 pm
why its geting data from dbo table but not from user1?
The previous statement about schema scope was incorrect, the user default schema is ignored. The compiled code first looks for the object in its own schema, then looks in dbo. When tested with a user with a default schema of dbo it appears to be reverting to default, but it reverts to dbo regardless of the default schema.
What is the requirement? Do you need different data for different users based on their default schema? or do you need code that will try one table first, then another if it doesn't exist? You could include a schema column in a single table and filter according to SCHEMA_NAME() or a conditional schema name based on the logic you need.
July 19, 2022 at 3:37 pm
Because I have one product whose code is common for all projects which come to us, so we have to keep one database for one project.
and now we want to keep one database for all projects with different schema. With this every thing went well.
But the scalar function needs two part names. So we decided to keep all scalar functions in one schema(common schema) so that we do not have to change the two part names because we have common code for a product.
This solution architecture means that you need to create new database objects every time there is a new project. This is poor design, in my opinion.
Instead, consider creating a master Project(ProjectCode PK, ...) table and adding ProjectCode to those tables which contain data for multiple projects. Adding new projects becomes a breeze after doing this.
If each project's data is stored in many tables and there is lots of data for each project, I'd also consider keeping the databases separate and migrating all common/shared code to a central 'Utility' database.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 21, 2022 at 5:24 am
"do you need code that will try one table first, then another if it doesn't exist? "
yes almost,
we want to login using login/user "login1" which has got tables in schema "s1" which is the default schema of login/user "login1" , then we would call a scalar function "sf" which is in schema "s2" like so s2.sf(1), this scalar function gets data from a table "t" which is not prefiex by any shcema in side the funciton "sf" and the table "t" resides in shecma "s1".
our plan is to make one schema for each project , in that way we will have one database and many schemas.
but the problem is scalar function needs two part names , which we have to write in stored procedure, so we got an idea to keep all scalar in one schema "s2" and then call this comman functions from all shcemas's stored procedures, in this way we do not have to change the two part name in each sp of each schema.
July 21, 2022 at 7:58 am
The problem appears to be you8 are relying on the default schema of a user to change the behaviour of your objects; that is the real problem here. You should always be using 2 part naming, not just when it suits you.
Like I mentioned before, instead of having multiple schemas with identical objects, have a single design with a column to denote what thing it belongs to (what ever your schema is currently being used to identical) and then use RLS or procedures to control what data people can access. Stop trying to make functions work a way they don't.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply