February 23, 2016 at 2:18 am
Hi,
In one of our application we have allowed users to log in with their respective Windows Login Id. These Login Ids are mapped in one Windows Domain Id and login of this Domain Id is created in SQL Server. The Domain Id is given public as Server Role and db_owner as database role.
Now when one of the procedures is executed via the application, it does not allow to create a physical table and throws error as "The specified schema name "" does not exist or you do not have permission to use it".
I found one work around i.e. to specify dbo in the procedure, but it will introduces huge efforts of changing other procedures and also its quality testing.
What can be the other solution.
Regards,
Saumik Vora
February 23, 2016 at 2:25 am
What's the default schema for the database user?
-- Gianluca Sartori
February 23, 2016 at 2:35 am
the default schema is un-defined hence by default it should be dbo.
February 23, 2016 at 2:37 am
saum70 (2/23/2016)
I found one work around i.e. to specify dbo in the procedure
That's not a workaround, that's the correct solution. All tables should be schema-qualified in procedures. You should start making that fix whenever you change procedures.
As a workaround, check the user's default schema and test whether setting it to 'dbo' will break anything else
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 23, 2016 at 4:21 am
On giving default schema as dbo or others in login properties window--> user mappings, following error pops up
" The DEFAULT_SCHEMA clause cannot be used with the Windows group or with principals mapped to certificates or asymmetric keys".
As mentioned, if we change in the procedures then huge efforts of change as well as quality testing will be required and currently its not possible.
Please guide.
February 23, 2016 at 5:05 am
Also I found irrational behavior. in some instance it is creating the table but when inserting into the same, it gives error as invalid object table name. In this case I debugged the procedure by inserting the command select * from sys.tables where name = 'table name' just after its creation statement.
When executing the procedure using SQL query analyzer with Windows login, I find that the table exists in sys.tables. however when inserting record into the same, it gives error.
February 23, 2016 at 5:40 am
You're getting inconsistent behavior because you're not providing the schema. That's the right way to do things. It might mean you need to go back and edit your code, but your code is wrong. If the code is wrong, how else do you fix it, but by changing it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply