asked
SQL Server 2008 R2 - SQL Login issue
Dear All, We are running SQL Server 2008 R2. We have created 2 SQL Logins having db_owner role assigned. Now we are facing following two issues: 1. We have created databases and those two users are coming by default in those databases. Now we have removed those users from Instance level Security Logins but in databases it is still coming. Is there any way that we can delete the user from all databases rather than by doing it one by one. 2. As I mentioned that we have removed those two SQL logins but when we are creating a new databases they are coming by default in those databases although they are removed from Instance > Security > Logins. How to compeletely remove the users so that they should appear again? Thanks in Advance
Have you assigned these logins as database users in the database called "model"? The model database is used as a template when creating new databases, anything you create in there (tables, stored procedures, users etc.) will be copied into the new database.
Dear WilliamD, Thanks for your prompt response. Yeah these two users were appearing in model database as well. So I have removed them from there so hopefully they should not come back again in any new database right? Any ideas about issue no 1 that how to remove these users from the database all at once Regards,
Now that they are out of the model database they will not automatically appear in any new databases. You will still have to remove the users from each database. However, as this is for two users and probably a one-off job, you can do this quite easily in the GUI. Open the login, then go to "User Mappings" and remove the check on each database that the user is in and should not be. Then save with "OK".
