May 24, 2010 at 11:30 am
I have a Server Login that is has sysadmin role. This login created several DBs on the server, and when I look at "User Mapping", I see that there are check marks on the DBs this login created. On these DBs, the User column has dbo and the Default Schema column is dbo... fine.
However, I also see check marks on DBs that this login didn't create. On these DBs, the User has has whatever the Login is and the Default schema is dbo. I thought this was redundant (and also confusing) since the Login has sysadmin role. Not sure why the previous DBA had it this way. My thought is if you are a sysadmin, then you don't need to be mapped to any database. (Am I correct about this?) So I unchecked all the other DBs where the Login is not the dbo and Mgmt Studio let me do that.
But there is one DB that it didn't let me uncheck saying the Login owns a schema in that database. And I do see in that DB this Login owns the schema "db_owner".
How did this happen and how do I delete this user from the database?
May 27, 2010 at 9:41 am
I think the only way the user could own the schema is if someone actually made it the owner. Is this one of the databases that the login created?
To fix it you have to assign an new owner for the schema, then drop the user from the database. You can change the schema owner in SSMS by opening the schema's properties and replacing the current owner with another user in the database.
Greg
May 27, 2010 at 9:47 am
Thank you for the response. I should've update this. I was able change the schema owner and then delete the user.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply