December 20, 2011 at 3:06 pm
Greetz!
I have some logins specified in my sql 2008 r2 instance that should not have login rights to anything on the instance. They were mistakenly added when we brought the dbs over from another server. The logins are Active Directory Accounts. I would like to remove them but am not sure of the consequences.
If I remove them from the Security/login area will that automatically remove them from the databases where they have been added?
When I tried to remove them from an individual database I was asked if I also wanted to delete the underlying schema. I didn't because I wasn't sure what that meant. If I delete that schema does it remove it only from that database or from every DB in the instance?
I'd like to clean these up so that it's more secure but also more clear to future techs but I also don't want to bring down the farm in the process!
Thanks!
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
December 20, 2011 at 3:24 pm
No, if you try deleting the server login the mapped database users will not be deleted as well. You'll have an orphaned user(s). You'll get a warning message when you go to delete the login about this.
When you tried to remove the individual user and got the message about deleting the underlying schema...that was probably because there is a schema in that database with the same name as the user you are trying to delete. Check under Security->Schema. I bet there's a matching schema in there.
December 20, 2011 at 3:46 pm
Yes. There are schemas that share the name fo the user. When I look at the properties of the user the 'Owned Schema' is the same as the user. If this user is dropped along with it's schema, will it only be for that database?
Even as a mother protects with her life
Her child, her only child,
So with a boundless heart
Should one cherish all living beings;
December 20, 2011 at 4:00 pm
Yeah, it will only drop for that database. You'd have to do the same process in every other database. Or script the process out. If there are any objects (tables, stored procs etc.) that are part of the schema you'll have to transfer them to a different schema. You can do this with alter schema.
December 20, 2011 at 5:13 pm
Or change the schema owner: http://blog.sqlauthority.com/2008/04/05/sql-server-2005-transferring-ownership-of-a-schema-to-a-user/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply