query to view current executing jobs

... I know i have asked this before and the response i got is run
sp_help_job.. Please bear with me as Im not a SQL guru . I would like to run
a script in QA and the output should give me the list of jobs that are
currently running. I have around 100 SQL Agent jobs on a server and instead
of refreshing my screen in EM to see the status of running, I want to see
those jobs only from within QA.
Can someone provide that query for me ? Would be highly appreciated.The procedure call is: exec msdb..sp_help_job
For each job, check the coding of current_execution_status:
0 Returns only those jobs that are not idle or suspended.
1 Executing.
2 Waiting for thread.
3 Between retries.
4 Idle.
5 Suspended.
7 Performing completion actions.
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uO370WdGFHA.2784@.TK2MSFTNGP10.phx.gbl...
> .. I know i have asked this before and the response i got is run
> sp_help_job.. Please bear with me as Im not a SQL guru . I would like to
run
> a script in QA and the output should give me the list of jobs that are
> currently running. I have around 100 SQL Agent jobs on a server and
instead
> of refreshing my screen in EM to see the status of running, I want to see
> those jobs only from within QA.
> Can someone provide that query for me ? Would be highly appreciated.
>|||You could try the following query, instead of returning all the jobs, it
just returns current active jobs.
--find Jobs that are currently running:
exec msdb..sp_get_composite_job_info @.enabled=1 , @.execution_status = 1
"JohnnyAppleseed" <someone@.microsoft.com> wrote in message
news:uI581odGFHA.3376@.TK2MSFTNGP14.phx.gbl...
> The procedure call is: exec msdb..sp_help_job
> For each job, check the coding of current_execution_status:
> 0 Returns only those jobs that are not idle or suspended.
> 1 Executing.
> 2 Waiting for thread.
> 3 Between retries.
> 4 Idle.
> 5 Suspended.
> 7 Performing completion actions.
>
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:uO370WdGFHA.2784@.TK2MSFTNGP10.phx.gbl...
> run
> instead
see
>|||Thanks Britney
Do you know what I can use to find just failed jobs ?
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:ObkP23eGFHA.584@.TK2MSFTNGP14.phx.gbl...
> You could try the following query, instead of returning all the jobs, it
> just returns current active jobs.
>
> --find Jobs that are currently running:
>
> exec msdb..sp_get_composite_job_info @.enabled=1 , @.execution_status = 1
>
>
> "JohnnyAppleseed" <someone@.microsoft.com> wrote in message
> news:uI581odGFHA.3376@.TK2MSFTNGP14.phx.gbl...
to
> see
>|||This is the same too right
exec msdb..sp_help_job @.enabled=1 , @.execution_status = 1
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:ObkP23eGFHA.584@.TK2MSFTNGP14.phx.gbl...
> You could try the following query, instead of returning all the jobs, it
> just returns current active jobs.
>
> --find Jobs that are currently running:
>
> exec msdb..sp_get_composite_job_info @.enabled=1 , @.execution_status = 1
>
>
> "JohnnyAppleseed" <someone@.microsoft.com> wrote in message
> news:uI581odGFHA.3376@.TK2MSFTNGP14.phx.gbl...
to
> see
>

Query to obtain users and privileges from databases

Hi to all, is my first post, i need a query or script to obtain all users and privileges from all my databases, someone to help me. I'm learning Administration SQL server 2005.

I know that sys.database_principals and sys.server_principals have information about that, but i need users - privileges of every database.

thank you

There are a couple of views that can help you here.

sys.server_permissions and sys.database_permissions will show what permissions are granted to logins and users respectively.

Do you need to only check for individual users or do you need to check for roles as well?

thx,

-steven

|||

The following solution provides a way to query all the permissions of all users in the current database. So it is not a complete answer to your question. In order to navigate in every database, it is easier to write managed code program to do that.

Select sys.database_principals.name, permission_name,state, state_desc

from sys.database_permissions, sys.database_principals

where grantee_principal_id = principal_id

|||Thank you very much for your answers

Query to obtain users and privileges from databases

Hi to all, is my first post, i need a query or script to obtain all users and privileges from all my databases, someone to help me. I'm learning Administration SQL server 2005.

I know that sys.database_principals and sys.server_principals have information about that, but i need users - privileges of every database.

thank you

There are a couple of views that can help you here.

sys.server_permissions and sys.database_permissions will show what permissions are granted to logins and users respectively.

Do you need to only check for individual users or do you need to check for roles as well?

thx,

-steven

|||

The following solution provides a way to query all the permissions of all users in the current database. So it is not a complete answer to your question. In order to navigate in every database, it is easier to write managed code program to do that.

Select sys.database_principals.name, permission_name,state, state_desc

from sys.database_permissions, sys.database_principals

where grantee_principal_id = principal_id

|||Thank you very much for your answers

Query to determine public access

Is there any T-SQL Script or SP that would determine what tables public users have access to? I only ask because I would like to make sure that any users with public rights do not have access to SYSXLOGINS, SYSDATABASES, SYSOBJECTS, SYSCOLUMNS and mswebtasks tables. Thanks
-Kyle

All users are members of the public role by default and out of the box public won't have any explicit permissions assigned to it. So unless you've tampered with these settings, you should be ok.

Remember if you assign explict deny permissions on public this could have unexpected consequences as ALL logins won't have access to those tables.

Id suggest having a user defined database role which you add all users that has the desired permissions.

HTH!

|||Being a DBA in a somewhat large company means that you inherit a lot of the main aspects of the job. Along with that, you can't satisfy auditers with the explanation that out of the box the public user role won't have rights to the tables. Thus, if anybody has a query that would tell the tables that the public user role has that would be fantastic.

Query taking ages for no apparent reason

Hope someone can help me with this because its driving me potty!

I have a .NET script that sends really simple queries to SQL server that works perfectly 50% of the time but for the other 50% it takes ages (2-3 minutes) and then fails, I'm assuming because it times out. I then check the SQL by excecuting it via query analyzer and it again takes ages but will work eventually (I'm assuming because this bypasses the timeout settings, but changing these isn't on).

This happens randomly, the scripts will be working fine and then fail a few times before magically working again!

Any ideas? Perhaps some database features that commonly cause this problem? The problem only occurs with one database, all our others are fine but we can't spot any differences!

Any help or tips would really be appreciated.

Thanks.sound like a locking issue.

When you are running the query via .net, in query analyzer in a seperate session run sp_who2. This will show you if there are any locked processes.

Even better use enterprise manager (if you have access)|||Originally posted by dbabren
sound like a locking issue.

When you are running the query via .net, in query analyzer in a seperate session run sp_who2. This will show you if there are any locked processes.

Even better use enterprise manager (if you have access)

Thanks for the advice.

There's no sign of locking when my problem is occuring using sp_who2 (I refreshed sp_who2 a few times whilst I was waiting for the query to give-up).

On the other hand, I had a look using enterprise manager->Locks/Object and there's a huge list of Table Locks (908!) owned by 'xact' (a transaction? )for the database i'm using. Other db's being used have database locks owned by the SESS (session I assume). I've never explicitly asked for a lock, but this db is someone else's so could there be somehting in there that aquires a lock?

Thanks for you help,

suddy.|||Suddy

Everytime you access the db, you will take a lock - the type and severity of that lock depends on what you are doing - have a look at locking in BOL (it can explain it better ..)

I find it easier to use locks/process id in Ent Manager as it is often easier to track the spid to a particular PC/trnsaction. It also tell you which process is blocking which other processes.

Another option may be to use profiler to track the SQL that is being ran, and capture blocking lock information - but this will have a performance impact itself (so be weary of it)|||Thanks for your help Dbabren.

Darned problem has mysteriously vanished this morning but I'm going to go away and have a look at BOL because this is bound to come back if I don't work out what's going on.

Thanks again.|||Originally posted by suddy
Hope someone can help me with this because its driving me potty!

I have a .NET script that sends really simple queries to SQL server that works perfectly 50% of the time but for the other 50% it takes ages (2-
Thanks.

Can you post the queries? Are you using the "NOLOCK" directive with your select statements?