Can't delete SQL user because they own objects

How do I find out what objects they own and then how do I drop those objects
so I can delete the user?Goto Security >>
Logins >>
Properties of the User you want to delete >>
"Database Access" Tab >>
under the "User" column , you will find all of the databases he owns
because "dbo" will be showing, then you can delete the db's if you want
"Dan Marth" <danmarth@.earthlink.net> wrote in message
news:Oo7Kj5bGEHA.3188@.TK2MSFTNGP10.phx.gbl...
> How do I find out what objects they own and then how do I drop those
objects
> so I can delete the user?
>|||Hi Dan
You could run the following:
select table_name, table_type from information_schema.tables
where table_schema = 'user_name'
This will return both tables and views. You can use DROP TABLE for the
tables and DROP VIEW for the views, and you must preface the object name
with the user_name of the owner in the drop command.
You could also just change the owner of these objects with
sp_ChangeObjectOwner if you don't want to drop them.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Dan Marth" <danmarth@.earthlink.net> wrote in message
news:Oo7Kj5bGEHA.3188@.TK2MSFTNGP10.phx.gbl...
> How do I find out what objects they own and then how do I drop those
objects
> so I can delete the user?
>|||So I have to do it table by table or is there someway to find out what
objects the user owns? There are close to a hundred tables in the database
that I want to delete the user from. Seems like there should be a better
way and yes I don't want to delete any database objects (I guess I worded
that wrong) just change ownership or whatever it takes to delete the user.
Thanks,
Dan
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23$NADUcGEHA.704@.tk2msftngp13.phx.gbl...
> Hi Dan
> You could run the following:
> select table_name, table_type from information_schema.tables
> where table_schema = 'user_name'
> This will return both tables and views. You can use DROP TABLE for the
> tables and DROP VIEW for the views, and you must preface the object name
> with the user_name of the owner in the drop command.
> You could also just change the owner of these objects with
> sp_ChangeObjectOwner if you don't want to drop them.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Dan Marth" <danmarth@.earthlink.net> wrote in message
> news:Oo7Kj5bGEHA.3188@.TK2MSFTNGP10.phx.gbl...
> objects
>|||Two possibilities, both of which use undocumented features:
1. Direct update of system tables. If you've never done this before, this is
not a good time to start.
update sysobjects
set uid = <user_id of some other user>
where uid = <uid of user you want to delete.
2. Use the undocumented procedure sp_execresultset to build a select
statement that generates the sp_changeobjectowner commands, and then
executes them. This is a little tricky, so you should practice in a test db
first.
First construct the select that builds the commands:
select 'exec sp_changeobjectowner [olduser.' + table_name + '], newuser
'
from information_schema.tables
where table_schema = 'olduser'
Substitute usernames as appropriate. When you run the above you should get a
whole bunch of EXEC sp_changeobjectowner commands in the result window. You
can either copy from the result window, and paste into the query window, and
execute, OR
wrap the whole thing in sp_execresultset.
All the quotes in the original command must be turned into two single quotes
in the new command, because the whole thing (the argument to
sp_execresultset) is wrapped in single quotes:
exec sp_execresultset 'select ''exec sp_changeobjectowner [olduser.'' +
table_name + ''], newuser'' from information_schema.tables
where table_schema = ''olduser'' '
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Dan Marth" <danmarth@.earthlink.net> wrote in message
news:#Fg$EqeGEHA.1396@.TK2MSFTNGP11.phx.gbl...
> So I have to do it table by table or is there someway to find out what
> objects the user owns? There are close to a hundred tables in the
database
> that I want to delete the user from. Seems like there should be a better
> way and yes I don't want to delete any database objects (I guess I worded
> that wrong) just change ownership or whatever it takes to delete the user.
> Thanks,
> Dan
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23$NADUcGEHA.704@.tk2msftngp13.phx.gbl...
>