November 17, 2010 at 11:46 am
Hi,
On my SSMS (sql server 2005), i have users created at the database level but they are not created on hte server level.
So, when I try to create it, with a user mapping, it throws an error that user, group or role already exists in the database.
And when i try to drop the user from database level, it says cant drop as it has a scheme attached to it.
How should i proceed.
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
November 17, 2010 at 1:16 pm
when i try to delete the schema, it says cannot delete the schema as it is refrenced by object 'xyz'
Regards,
Sushant
Regards
Sushant Kumar
MCTS,MCP
November 17, 2010 at 1:28 pm
SKYBVI (11/17/2010)
when i try to delete the schema, it says cannot delete the schema as it is referenced by object 'xyz'Regards,
Sushant
You need to go to the object and change the schema (typically to dbo), then drop the schema, then drop the user.
Schemas have their place, but I think they are a bit loosely implemented in SQL 2005 and 2008, particularly the automatic creation of a schema every time you add a standard user to a database, and the fact that objects created under that user are automatically owned by the schema.
To prevent this you need to either expressly remove the schemas, or always use CREATE OBJECT dbo.Objectname... This is something I've never been able to teach a developer to consistently do, and I've seen applications using standard logins, that create their own objects as they need them with some really serious object spaghetti, particularly if the account has changed at some stage.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply