**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 grateful
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...
> 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
DB2View2
DB2View3
DB2Storeprocedure1
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
>DB2View2
>DB2View3
>DB2Storeprocedure1
>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:

**Current Activity(Enterprise Manager)**

Hi
I'm using SQL server 2000 and when I select the current activity in
enterprise manager and want to sort the CPU column ascending, it seels by
clicking on the title of this column it will be sort alphabetically not in
numeric mode,why?
and the second problem is,by looking through this page how can I
understand which users or application are using the resources on server
more? and is there anyway to find if an object locked or busy for a long
time with a special program on specific station?
Fore example: I've a specific procedure that most of time work probably(2
sec) but sometimes it takes a long time to fetch the result (5 min)!! and
by renaming it ,it works well again,can this page help me to find which
station is running this query during that special "5 minute" time?
Any help would be thankful
The CPU data is probably converted to a character behind the scenes. Take a
look at sp_who2 and sp_lock sp's to get a better picture of what is
happening. These are more flexible than EM and you can look at the code for
them in the master db to see what they are doing. You may also want to run
a trace to capture more information about who is doing what and how long
each takes. Lookup trace or profiler in BooksOnLine.
Andrew J. Kelly SQL MVP
<R> wrote in message news:opssw5yamdmw7tkz@.system109.parskhazar.net...
> Hi
> I'm using SQL server 2000 and when I select the current activity in
> enterprise manager and want to sort the CPU column ascending, it seels by
> clicking on the title of this column it will be sort alphabetically not in
> numeric mode,why?
> and the second problem is,by looking through this page how can I
> understand which users or application are using the resources on server
> more? and is there anyway to find if an object locked or busy for a long
> time with a special program on specific station?
> Fore example: I've a specific procedure that most of time work probably(2
> sec) but sometimes it takes a long time to fetch the result (5 min)!! and
> by renaming it ,it works well again,can this page help me to find which
> station is running this query during that special "5 minute" time?
> Any help would be thankful
|||Thanks, I tried to save activities by creating a trace saving result to
table.
Although I filtered it just to save some activities for some logins ,it
needs a lot space to store all details in a table for some days!!!
I want to find a way to record most activities in details to refer to them
later to find who and when sth happened.
can you direct me more?
Thanks a lot
|||You should never send a trace directly to a table, especially if it is long
term. You should send the results directly to a file on a local disk drive.
You can then load them into a table later with fn_trace_gettable. Don't use
profiler for this but rather sp_trace_create and it's associated sp's. See
here for more details:
http://vyaskn.tripod.com/server_side...sql_server.htm
Andrew J. Kelly SQL MVP
<R> wrote in message news:opss0idcllmw7tkz@.system109.parskhazar.net...
> Thanks, I tried to save activities by creating a trace saving result to
> table.
> Although I filtered it just to save some activities for some logins ,it
> needs a lot space to store all details in a table for some days!!!
> I want to find a way to record most activities in details to refer to them
> later to find who and when sth happened.
> can you direct me more?
> Thanks a lot