cannot add windows user, cannot delete sql user

[tbone]

We were using sql authentication on our sql server 2000 servers, But
need to use windows authentication. I have an sql user jdoe that owns
many different objects in multiple databases. I have tried adding the
windows login of jdoe with the exact same permissions as the sql user
jdoe, but it complains because it says jdoe already exists. So I
attempt to delete the sql user of jdoe (remembering the permissions so
I can reapply them to the windows user soon to be created) But it says
"you cannot drop the selected login id because that login id owns
objects in one or more databases" So now I am trying to figure out how
to change the ownership of those many many objects in the many
databases from jdoe to dbo so I can delete this sql user of jdoe. Does
anyone know of a way to convert an sql login to a windows login? Or if
that can't be done, how to change the owner of many objects to dbo so I
can delete this user? Maybe a T-SQL script or stored procedure. There
is a stored procedure called sp_changeobjectowner but that only works
for one object, how would I do that on all the objects that jdoe owns?
As you can see I am a newbie but I appreciate any help you can
provide!!! Thanks in advance!

[Simon Hayes]

"tbone" <to**********@gmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

We were using sql authentication on our sql server 2000 servers, But
need to use windows authentication. I have an sql user jdoe that owns
many different objects in multiple databases. I have tried adding the
windows login of jdoe with the exact same permissions as the sql user
jdoe, but it complains because it says jdoe already exists. So I
attempt to delete the sql user of jdoe (remembering the permissions so
I can reapply them to the windows user soon to be created) But it says
"you cannot drop the selected login id because that login id owns
objects in one or more databases" So now I am trying to figure out how
to change the ownership of those many many objects in the many
databases from jdoe to dbo so I can delete this sql user of jdoe. Does
anyone know of a way to convert an sql login to a windows login? Or if
that can't be done, how to change the owner of many objects to dbo so I
can delete this user? Maybe a T-SQL script or stored procedure. There
is a stored procedure called sp_changeobjectowner but that only works
for one object, how would I do that on all the objects that jdoe owns?
As you can see I am a newbie but I appreciate any help you can
provide!!! Thanks in advance!

First, change the owner of all jdoe's objects to dbo - one way is to execute
the output of a query like this one:

select 'exec sp_changeobjectowner ''' + name + ''', ''dbo'''
from sysobjects
where uid = user_id('jdoe')

Then drop the jdoe database user and login, create the new login for the
Windows account, and give it database access. If you want the new user to
own the objects again, then just do a quick find and replace on the original
script to change 'dbo' to 'jdoe' or 'MyDomain\jdoe' or whatever the database
user name is.

Simon

[t bone]

Then drop the jdoe database user and login, create the new login for the
Windows account, and give it database access. If you want the new user
to
own the objects again, then just do a quick find and replace on the
original
script to change 'dbo' to 'jdoe' or 'MyDomain\jdoe' or whatever the
database
user name is.
After I change the ownership to dbo, all the objects in the database
will be dbo now, I guess I can change those objects to a temp user so I
can distinguish them from the rest of the dbo objects. Anyway, is there
any way i could do this by changing the uid mappings or something? Each
database has a user that links to a login correct? Is there a way to
link the already existing user to the new windows login?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

t bone (bi*********@yahoo.com) writes:

After I change the ownership to dbo, all the objects in the database
will be dbo now, I guess I can change those objects to a temp user so I
can distinguish them from the rest of the dbo objects. Anyway, is there
any way i could do this by changing the uid mappings or something? Each
database has a user that links to a login correct? Is there a way to
link the already existing user to the new windows login?

Don't think so. I was about to suggest trying sp_change_users_login, but I
see in Books Online that it doesn't work for Windows logins.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp