Deleting Database users in 2005

We constantly import DB's from our different customers for research
issues to our server. In the past after the DB was restored I would
then go and delete the users from the old network to keep it clean but
now with 2005 it seems like there is no easy way of doing it in MS
management studio.
Deleting users froma DB in 2000 was very simple all I had to do was
highlight all the users in EM and then hit the delete key to delete all
the highlighted users. In 2005 I have not found a way to delete
multiple users at one shot, it seems like it requires you to delete one
user at a time. Is there any other way of doing it in MS management
studio?
Also when I restore a 2000 database from a different server on a 2005
server it automatically upgrades the DB to 2005, which is cool however
when I try to delete the orphan users that do not belong to the
original network from the restored DB on the 2005 server I get an
error "drop failed for user xxx", I then have to delete the schema for
that user before I can delete the user. In 2000, I was always able to
delete them with no issues.
I am sure many of you guys have run into this issue also and I was
wondering if there was a better way of doing it.
Any tips or knowhow in this issue will be greatly appreciated.
ThanksThe best way to drop multiple uses is with a script using the command drop
login XXX
I don't believe there is a way to do this with SSMS. I don't know of a way
of fixing the orphaned users through SSMS either.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"shub" <shubtech@.gmail.com> wrote in message
news:1161102716.034164.30650@.e3g2000cwe.googlegroups.com...
> We constantly import DB's from our different customers for research
> issues to our server. In the past after the DB was restored I would
> then go and delete the users from the old network to keep it clean but
> now with 2005 it seems like there is no easy way of doing it in MS
> management studio.
> Deleting users froma DB in 2000 was very simple all I had to do was
> highlight all the users in EM and then hit the delete key to delete all
> the highlighted users. In 2005 I have not found a way to delete
> multiple users at one shot, it seems like it requires you to delete one
> user at a time. Is there any other way of doing it in MS management
> studio?
> Also when I restore a 2000 database from a different server on a 2005
> server it automatically upgrades the DB to 2005, which is cool however
> when I try to delete the orphan users that do not belong to the
> original network from the restored DB on the 2005 server I get an
> error "drop failed for user xxx", I then have to delete the schema for
> that user before I can delete the user. In 2000, I was always able to
> delete them with no issues.
> I am sure many of you guys have run into this issue also and I was
> wondering if there was a better way of doing it.
> Any tips or knowhow in this issue will be greatly appreciated.
> Thanks
>|||shub wrote:
> We constantly import DB's from our different customers for research
> issues to our server. In the past after the DB was restored I would
> then go and delete the users from the old network to keep it clean but
> now with 2005 it seems like there is no easy way of doing it in MS
> management studio.
> Deleting users froma DB in 2000 was very simple all I had to do was
> highlight all the users in EM and then hit the delete key to delete all
> the highlighted users. In 2005 I have not found a way to delete
> multiple users at one shot, it seems like it requires you to delete one
> user at a time. Is there any other way of doing it in MS management
> studio?
Don't use the GUI to do this, write a script and save yourself some
pain...
> Also when I restore a 2000 database from a different server on a 2005
> server it automatically upgrades the DB to 2005, which is cool however
> when I try to delete the orphan users that do not belong to the
> original network from the restored DB on the 2005 server I get an
> error "drop failed for user xxx", I then have to delete the schema for
> that user before I can delete the user. In 2000, I was always able to
> delete them with no issues.
According to the Books Online entry for sp_dbcmptlevel:
"When a database is upgraded to SQL Server 2005 from any earlier
version of SQL Server, the database retains its existing compatibility
level. This applies to both system and user databases. Use
sp_dbcmptlevel to change the compatibility level of the database to 90.
To view the current compatibility level of a database, query the
compatibility_level column in the sys.databases catalog view."
Based on this, I would assume that if you restore a SQL 2000 database
backup using the RESTORE command, and not the GUI, your newly restored
database will remain in SQL 2000 compatibility mode.
> I am sure many of you guys have run into this issue also and I was
> wondering if there was a better way of doing it.
Nope, because I don't use the GUI. The "better way of doing it" is via
T-SQL commands and scripts.