November 3, 2009 at 2:23 pm
Hello,
I have a database user that has their own schema, which is used by their application to connect to the database.
I have given the user dbo rights to the database.
Initially the user's default schema was their own user name, however, when they created db objects it would create it with their schema instead of dbo.
I tried making dbo the owner of the users schema to have new objects created by the user save as dbo schema but this did not work.
I would appreciate any advise one could provide!
Thanks!!
HawkeyeDBA
November 3, 2009 at 2:33 pm
I'm a bit confused on what you want to accomplish here. Are you wanting to move the objects from the user's schema into dbo or just change their default schema so new objects created by that user end up in dbo?
November 3, 2009 at 2:37 pm
Sorry for the confusion, I want their default schema to be dbo. BUT, when I change their default schema and they try to create an object, through their application, it still uses their old schema.
thoughts?
November 3, 2009 at 2:42 pm
I assume then that the application is using their login? Have you tried logging in as that user via Management Studios and reproducing this behavior? This may make it easier to troubleshoot as opposed to having to work with the application.
Just curiuos, when you changed the default schema, didn't that break the application? The tables that it had already created in the user schema would no longer be available to it unless, of course, it is referencing them with the full schema.table reference.
November 3, 2009 at 3:02 pm
I can try to login as them into SSMS, that's a good thought.
Yeah, the application is weird. The user connects to a service, and then the service connects to the database with it's own schema. The user only has 2 tables, which are referenced by the service, under their schema name.
I will try SSMS and let you know.
Thanks!
HawkeyeDBA
November 4, 2009 at 9:46 pm
well that worked, but the quirky thing with the application is this, the user has to have two tables (that the application creates) with the user name as the schema on them.
After those two tables are created, I believe I need to set their default schema back to dbo so that when they create objects, the schema will be dbo.
This is an ESRI application, if anyone has any experience with this I'm all ears!
Thanks!!
Hawkeye DBA
November 5, 2009 at 9:52 am
If you have a development environment that you could play around in, this may be worth setting up a Profiler trace to see what the app is doing. If you could get it set up to where the permissions are correct and you don't have to make chagnes based on the state of the application, that would be huge. If the application is creating the 2 tables that it needs within the user's schema by explicitly referencing the schema name like 'CREATE TABLE UserSchema.MyTable....' then you can just set the default schema to DBO and grant them the appropriate permissions within the User Schema to create tables.
If they create the table w/o referencing the schema, but they do reference the DBO schema when creating/accessing tables there, make the default schema the User Schema and grant them the appropriate permissions within DBO.
This would be a good question for the aplication vendor as well.
November 9, 2009 at 9:50 am
Thanks Hall of Fame. I thought about using profiler so I will do this today on my test environment while the user creates objets. I have requested a call from the vendor as well. It looks like the application intends to either have the user direct connect or connect through a service but not both, so we are obviously doing something out of the norm here. 🙂 imagine that!
Ok, I will post back what comes of more testing.
Thanks again,
Hawkeye DBA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply