**find related objects**

Hi
I'm working with SQL 2000, how can I find login name L1 is the owner of
which objects() in which dbs?in other word, when I want to delete a login
name in login list an error appears with the context of "you can n't
delete it because it's owns some objects in some DBs".
any help would be gratefulTry this one
CREATE FUNCTION dbo.RoleCheckUser
(
@.UserName sysname,
@.RoleName sysname
)
RETURNS BIT
AS
BEGIN
DECLARE @.RetVal BIT
SET @.RetVal = 0
SELECT @.RetVal = 1
WHERE EXISTS
(
SELECT *
FROM sysmembers membs
JOIN sysusers users on membs.memberuid = users.uid
JOIN sysusers groups on membs.groupuid = groups.uid
WHERE
users.name = @.UserName
AND groups.name = @.RoleName
)
RETURN @.RetVal
END
GO
-- Syntax to use the created function
SELECT dbo.RoleCheckUser('dbo', 'db_owner')
G
<R> wrote in message news:ops47uo6j3mw7tkz@.system109.parskhazar.net...
> Hi
> I'm working with SQL 2000, how can I find login name L1 is the owner of
> which objects() in which dbs?in other word, when I want to delete a login
> name in login list an error appears with the context of "you can n't
> delete it because it's owns some objects in some DBs".
> any help would be grateful|||Thanks for your reply, but let me explain my question in another way,
I want a list which shows me L1 had created which objects for example:
L1:
--
DB1 View1
DB1 table41
DB2 View2
DB2 View3
DB2 Storeprocedure1
then I can go to those databases and I can change the owner of each object
and finally be able to delete th L1 completey.
I would be grateful if you could help me.
On Sun, 19 Feb 2006 13:00:44 +0200, Uri Dimant <urid@.iscar.co.il> wrote:

> Try this one
> CREATE FUNCTION dbo.RoleCheckUser
> (
> @.UserName sysname,
> @.RoleName sysname
> )
> RETURNS BIT
> AS
> BEGIN
> DECLARE @.RetVal BIT
> SET @.RetVal = 0
> SELECT @.RetVal = 1
> WHERE EXISTS
> (
> SELECT *
> FROM sysmembers membs
> JOIN sysusers users on membs.memberuid = users.uid
> JOIN sysusers groups on membs.groupuid = groups.uid
> WHERE
> users.name = @.UserName
> AND groups.name = @.RoleName
> )
> RETURN @.RetVal
> END
> GO
> -- Syntax to use the created function
> SELECT dbo.RoleCheckUser('dbo', 'db_owner')
> G
>
> <R> wrote in message news:ops47uo6j3mw7tkz@.system109.parskhazar.net...
>
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/|||You can query sysobjects in a database to find all objects
owned by a user - something like:
SELECT name, xtype
FROM sysobjects
WHERE uid = user_id('YourUser')
-Sue
On Mon, 20 Feb 2006 08:18:40 +0330, R-M <R> wrote:
[vbcol=seagreen]
>Thanks for your reply, but let me explain my question in another way,
>I want a list which shows me L1 had created which objects for example:
>L1:
>--
>DB1 View1
>DB1 table41
>DB2 View2
>DB2 View3
>DB2 Storeprocedure1
>then I can go to those databases and I can change the owner of each object
>and finally be able to delete th L1 completey.
>I would be grateful if you could help me.
>On Sun, 19 Feb 2006 13:00:44 +0200, Uri Dimant <urid@.iscar.co.il> wrote:
>