Grant user to see the SQL Server job status

Hi
I need to grant permission to the user to see the sql server job status
through Enterprise Manager. I did the following steps but still these user
s
are not able to see the sql server jobs status. Even few jobs are running
these users always shows as " not running" .
1 Created a separate role to in MSDB to monitor daily sql servers jobs. In
this role granted target server roles and execute permission to few stored
procs which the user can start and stop .
2 I dont want to give sysadmin rights to these users.
Need your info
Regards
SwamiWhat about building up a view which selects the appropiate columns ? YOu can
give the user the appopiate permissions on the view.
USE Northwind
GO
CREATE VIEW dbo.JobView
AS
Select somecolumnsofthatcolumnse from msdb..sysjobs sj
INNER JOIN msdb..sysjobsteps sjs
ON sj.job_id = sjs.Job_id
GO
Select * from jobview
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Swami" <Swami@.discussions.microsoft.com> schrieb im Newsbeitrag
news:85FECA89-84E8-4F98-A2E9-1C71CE56A413@.microsoft.com...
> Hi
> I need to grant permission to the user to see the sql server job status
> through Enterprise Manager. I did the following steps but still these
> users
> are not able to see the sql server jobs status. Even few jobs are running
> these users always shows as " not running" .
> 1 Created a separate role to in MSDB to monitor daily sql servers jobs.
> In
> this role granted target server roles and execute permission to few stored
> procs which the user can start and stop .
> 2 I dont want to give sysadmin rights to these users.
> Need your info
> Regards
> Swami
>
>|||Add the user to the RepositoryUser role in msdb. This is a new role
added in sp3. users must have this role in order to view the staus of
jobs
Swami wrote:
> *Hi
> I need to grant permission to the user to see the sql server job
> status
> through Enterprise Manager. I did the following steps but still
> these users
> are not able to see the sql server jobs status. Even few jobs are
> running
> these users always shows as " not running" .
> 1 Created a separate role to in MSDB to monitor daily sql servers
> jobs. In
> this role granted target server roles and execute permission to few
> stored
> procs which the user can start and stop .
> 2 I dont want to give sysadmin rights to these users.
> Need your info
> Regards
> Swami *
capitanou
---
Posted via http://www.mcse.ms
---
View this thread: http://www.mcse.ms/message1631364.html

Grant job view and execute

Hello,
How can i grant a user to view and execute Jobs on SqlSrv2K without grant
SystemAdministratr previleges.
Thanks
Leandro Loureiro dos SantosLeonardo
BOL says
How to give others ownership of a job (Enterprise Manager)
To give others ownership of a job
1.. In the details pane, right-click the job, and then click Properties.
2.. In the Owner list, select a login.
Assigning a job to another login does not guarantee that the new owner has
sufficient permission to run the job successfully.
"Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
news:uD5wcNH4DHA.2380@.TK2MSFTNGP11.phx.gbl...
> Hello,
> How can i grant a user to view and execute Jobs on SqlSrv2K without grant
> SystemAdministratr previleges.
> Thanks
> Leandro Loureiro dos Santos
>|||Actually, i need non-system administrators users could view and execute a
job.
How can i do that?
"Leandro Loureiro dos Santos" <leandro@.email.com> escreveu na mensagem
news:uD5wcNH4DHA.2380@.TK2MSFTNGP11.phx.gbl...
> Hello,
> How can i grant a user to view and execute Jobs on SqlSrv2K without grant
> SystemAdministratr previleges.
> Thanks
> Leandro Loureiro dos Santos
>

Grant job view and execute

Hello,
How can i grant a user to view and execute Jobs on SqlSrv2K without grant
SystemAdministratr previleges.
Thanks
Leandro Loureiro dos SantosLeonardo
BOL says
How to give others ownership of a job (Enterprise Manager)
To give others ownership of a job
1.. In the details pane, right-click the job, and then click Properties.
2.. In the Owner list, select a login.
Assigning a job to another login does not guarantee that the new owner has
sufficient permission to run the job successfully.
"Leandro Loureiro dos Santos" <leandro@.email.com> wrote in message
news:uD5wcNH4DHA.2380@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Hello,
> How can i grant a user to view and execute Jobs on SqlSrv2K without grant
> SystemAdministratr previleges.
> Thanks
> Leandro Loureiro dos Santos
>

---

|||Actually, i need non-system administrators users could view and execute a
job.
How can i do that?
"Leandro Loureiro dos Santos" <leandro@.email.com> escreveu na mensagem
news:uD5wcNH4DHA.2380@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Hello,
> How can i grant a user to view and execute Jobs on SqlSrv2K without grant
> SystemAdministratr previleges.
> Thanks
> Leandro Loureiro dos Santos
>

---

sql

Grant Control Causes SQL Server Agent job to fail?

I have a SQL Agent job that applies permissions to a SQL Server 2005
database after it has been restored, it executes in management studio,
it even parses when you click parse with the job step. Yet it fails
every time unless I remove this:
GRANT CONTROL ON OBJECT::dbo.usp_AStoredProc
TO "aDomain\aUser"
Errr... Bug?dba
It has been restored from SQL Server 2000? If it has , check out compatibily
level of the database (should be 90)
"dba" <bryanmurtha@.gmail.com> wrote in message
news:1178669352.091335.225000@.n59g2000hsh.googlegroups.com...
>I have a SQL Agent job that applies permissions to a SQL Server 2005
> database after it has been restored, it executes in management studio,
> it even parses when you click parse with the job step. Yet it fails
> every time unless I remove this:
> GRANT CONTROL ON OBJECT::dbo.usp_AStoredProc
> TO "aDomain\aUser"
> Errr... Bug?
>|||"Fails" doesn't give us much to go on. Specify an output file for that job step and post the error
messages here.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"dba" <bryanmurtha@.gmail.com> wrote in message
news:1178669352.091335.225000@.n59g2000hsh.googlegroups.com...
>I have a SQL Agent job that applies permissions to a SQL Server 2005
> database after it has been restored, it executes in management studio,
> it even parses when you click parse with the job step. Yet it fails
> every time unless I remove this:
> GRANT CONTROL ON OBJECT::dbo.usp_AStoredProc
> TO "aDomain\aUser"
> Errr... Bug?
>

Grant Control Causes SQL Server Agent job to fail?

I have a SQL Agent job that applies permissions to a SQL Server 2005
database after it has been restored, it executes in management studio,
it even parses when you click parse with the job step. Yet it fails
every time unless I remove this:
GRANT CONTROL ON OBJECT::dbo.usp_AStoredProc
TO "aDomain\aUser"
Errr... Bug?
dba
It has been restored from SQL Server 2000? If it has , check out compatibily
level of the database (should be 90)
"dba" <bryanmurtha@.gmail.com> wrote in message
news:1178669352.091335.225000@.n59g2000hsh.googlegr oups.com...
>I have a SQL Agent job that applies permissions to a SQL Server 2005
> database after it has been restored, it executes in management studio,
> it even parses when you click parse with the job step. Yet it fails
> every time unless I remove this:
> GRANT CONTROL ON OBJECT::dbo.usp_AStoredProc
> TO "aDomain\aUser"
> Errr... Bug?
>
|||"Fails" doesn't give us much to go on. Specify an output file for that job step and post the error
messages here.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"dba" <bryanmurtha@.gmail.com> wrote in message
news:1178669352.091335.225000@.n59g2000hsh.googlegr oups.com...
>I have a SQL Agent job that applies permissions to a SQL Server 2005
> database after it has been restored, it executes in management studio,
> it even parses when you click parse with the job step. Yet it fails
> every time unless I remove this:
> GRANT CONTROL ON OBJECT::dbo.usp_AStoredProc
> TO "aDomain\aUser"
> Errr... Bug?
>

Grant Control Causes SQL Server Agent job to fail?

I have a SQL Agent job that applies permissions to a SQL Server 2005
database after it has been restored, it executes in management studio,
it even parses when you click parse with the job step. Yet it fails
every time unless I remove this:
GRANT CONTROL ON OBJECT::dbo.usp_AStoredProc
TO "aDomain\aUser"
Errr... Bug?dba
It has been restored from SQL Server 2000? If it has , check out compatibily
level of the database (should be 90)
"dba" <bryanmurtha@.gmail.com> wrote in message
news:1178669352.091335.225000@.n59g2000hsh.googlegroups.com...
>I have a SQL Agent job that applies permissions to a SQL Server 2005
> database after it has been restored, it executes in management studio,
> it even parses when you click parse with the job step. Yet it fails
> every time unless I remove this:
> GRANT CONTROL ON OBJECT::dbo.usp_AStoredProc
> TO "aDomain\aUser"
> Errr... Bug?
>|||"Fails" doesn't give us much to go on. Specify an output file for that job s
tep and post the error
messages here.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"dba" <bryanmurtha@.gmail.com> wrote in message
news:1178669352.091335.225000@.n59g2000hsh.googlegroups.com...
>I have a SQL Agent job that applies permissions to a SQL Server 2005
> database after it has been restored, it executes in management studio,
> it even parses when you click parse with the job step. Yet it fails
> every time unless I remove this:
> GRANT CONTROL ON OBJECT::dbo.usp_AStoredProc
> TO "aDomain\aUser"
> Errr... Bug?
>sql

grande dbcc shrinkfile confusion

Okay - here's what's happening. I have a weekly job which uses dbcc shrinkfi
le to shrink a 60GB database down to a 40GB database. Usually runs okay - ta
kes about 10 hours - but recently it has been failing with error 3140 after
running for the 10 hours. T
he DB shows as still being 60GB but when I start the job again it runs for a
couple seconds, shows success, and suddenly the DB is 40GB. Does anyone kno
w why this is happening? There is another job that shrinks all the DBs on th
at server using the MS SQL
shrink option and it runs toward the end of the dbcc shrinkfile job. It has
never interfered with it before but it's the only other thing happening. Cou
ld it be the problem?Kimmy
This may be a silly question but if the database grows to 60GB every week, w
hy do you keep shrinking it? It obviously needs to be 60gb. By constantly sh
rinking it all you are doing is creating a situation were you will get fragm
entation at both the operat
ing system and the database level.
Regardless, a shrinkfile should not take 10 hours. You are running something
else that stops it from running. A full maintenance plan maybe? Check what
is running at that time and if you really must run it, run it at another tim
e.
Rergards
John|||Hi,
My recommendation would be to do this DBCC command at a low-use time in any
case. I feel the other job might have interferred.
If you are very concerned about performance hits you could first try DBCC
SHRINKDATABASE (or SHRINKFILE) with TRUNCATEONLY.
This will only pick empty pages from the end of the file or database without
doing any reorganization.
This option will take only very less time.
Thanks
Hari
MCDBA
"kimmy" <anonymous@.discussions.microsoft.com> wrote in message
news:38E133F3-7B71-4610-AC92-4D5889B5E8CE@.microsoft.com...
> Okay - here's what's happening. I have a weekly job which uses dbcc
shrinkfile to shrink a 60GB database down to a 40GB database. Usually runs
okay - takes about 10 hours - but recently it has been failing with error
3140 after running for the 10 hours. The DB shows as still being 60GB but
when I start the job again it runs for a couple seconds, shows success, and
suddenly the DB is 40GB. Does anyone know why this is happening? There is
another job that shrinks all the DBs on that server using the MS SQL shrink
option and it runs toward the end of the dbcc shrinkfile job. It has never
interfered with it before but it's the only other thing happening. Could it
be the problem?|||There is no maintenance running at that time on that server, except the othe
r shrinks which do happen toward the end. How long is a shrink on a 60 GB da
tabase supposed to take? I have other large databases (between 12 and 20 GB)
that take at least a few h
ours to run so I am not disturbed by how long it takes, only that it runs fo
r the full time, appears to fail, but when run again immediately shrinks the
database by 20 GB and returns success. You're right it is silly to have to
shrink it every week by so
much, but the vendor application my customers use is so badly written that a
third of the database fills up with white space every week. If I don't keep
shrinking it, it we will be up over 100 GB within three weeks and no backup
s will run.|||Kimmy
I must say I don't know how long a shrink takes as I never shrink databases,
but I thought 10 hours sounded excessive. The white space you are talking a
bout is this empty pages cause by file fragmentation? You would be better of
f rebuilding your tables th
an shrinking the database. If the growth in your database is not caused by d
ata growth but high data change, there should be a better way to stabilise i
t.
I would think either dbcc dbreindex or create index with Drop_existing param
eter should be your way to go.
Regards
John|||I guess I need to change the way we are doing the shrink - Thanks everyone f
or your help and advice.sql

Good SQL Server classes?

I am experienced DB2 UDB and Oracle DBA who needs to get back into SQL Server DBA support for my current job since we have a need for another SQL Server DBA in addition to my role as the primary DB2 guy. What Microsoft training courses would be best for experienced DBA who needs to ramp up as an MS SQL Server DBA? Thanks

ScottMicrosoft just created a course for experienced dba's to get to know SQL Server quickly. See http://www.microsoft.com/traincert/syllabi/2723Afinal.asp Don't know the quality of this course though.

good SQL book?

hello, for a new job i might have to learn SQL. i've never
worked with databases or SQL, so i'll need to learn. can
anybody advice me on what would be a good book to learn
from? i'm quite an experienced programmer, so it doesn't
have to be a dummies guide, and preferably not a bulky book
like the "SQL bible" or something.

oh, one of my 'favourite' computer books of all times is
"thinking in Java" by bruce eckel, to give you an idea.

mike

--
not sure if there's a better group to ask these questionsmichael nieuwenhuizen wrote:

> hello, for a new job i might have to learn SQL. i've never
> worked with databases or SQL, so i'll need to learn. can
> anybody advice me on what would be a good book to learn
> from? i'm quite an experienced programmer, so it doesn't
> have to be a dummies guide, and preferably not a bulky book
> like the "SQL bible" or something.
> oh, one of my 'favourite' computer books of all times is
> "thinking in Java" by bruce eckel, to give you an idea.
> mike
> --
> not sure if there's a better group to ask these questions

Probably the best of breed is Joe Celko's "SQL For Smarties" but I
wouldn't call it a beginner's book by any stretch of the imagination.
Put it on your list for book number 2 or 3.

Also, remember that TransactSQL is no more generic than is Oracle's
PL/SQL or any other vendor's implementation. So make sure the SQL book
you get is one that is oriented toward the RDBMS on which you will be
working.
--
Daniel Morgan
http://www.outreach.washington.edu/...oad/oad_crs.asp
http://www.outreach.washington.edu/...aoa/aoa_crs.asp
damorgan@.x.washington.edu
(replace 'x' with a 'u' to reply)|||"Daniel Morgan" <damorgan@.x.washington.edu> wrote
> Probably the best of breed is Joe Celko's "SQL For Smarties" but I
> wouldn't call it a beginner's book by any stretch of the imagination.

i actually browsed through that one and it looked pretty impressive ...
and then i saw it wasn;t for the beginner ...

> Put it on your list for book number 2 or 3.

will do. thanks.

> Also, remember that TransactSQL is no more generic than is Oracle's
> PL/SQL or any other vendor's implementation.

i really don't have a clue about SQL. aren't all the SQL's based on
one version?

> So make sure the SQL book you get is one that is oriented toward the
> RDBMS on which you will be working.

it would probably be Oracle.

mike

--
and i have no experience with that either|||michael nieuwenhuizen wrote:

> i really don't have a clue about SQL. aren't all the SQL's based on
> one version?

SQL is an ANSI standard language. But the ANSI standard is not one thing
... it is at least three. And within that standard every vendor has lots
of room to implement that standard by any means they wish. Then they all
add proprietary language extensions to differentiate their product from
the others (more cynical minds would say to lock in their customers).

>>So make sure the SQL book you get is one that is oriented toward the
>>RDBMS on which you will be working.
>
> it would probably be Oracle.
> mike

Then I'd suggest reposting your question in an Oracle usenet group (such
as comp.databases.oracle.server) so that we don't offend our hosts by
being off-topic. But you can find all of the Oracle SQL you could
possibly hope to find at http://tahiti.oracle.com.

--
Daniel Morgan
http://www.outreach.washington.edu/...oad/oad_crs.asp
http://www.outreach.washington.edu/...aoa/aoa_crs.asp
damorgan@.x.washington.edu
(replace 'x' with a 'u' to reply)|||The Guru's Guide to Transact-SQL by Ken Henderson

http://www.amazon.com/exec/obidos/t...536720?v=glance

global update

Hi all, I need to update some data in a table, based on some criteria.
In this case we are talking about the stamping of a price against a job.
The update table holds the jobs, and the update_details table holds the
activities performed on each job and the cost for each activity. If i
pull back this information using the following code

select t1.reference,t1.update_id, t2.*
from update t1, update_details t2
where left(t1.reference,2) in ('EA','ND','SD','ST')
and t1.update_id = t2.update_id

I get something like

EA 1883 Act1 4.20
EA 1883 Act2 3.00
EA 1883 Act3 7.50
EA 2444 Act1 4.20
SD 5433 Act1 5.60

I need to update the cost for everything pulled back using the above
sql, to a price determined in another table (activities)

the activities table would look something like

Activity_Code Cost_London Cost_Roc
Act1 5.60 4.20
Act2 4.00 3.00
Act3 6.20 5.60

in a nutshell i need to update the cost in the update details from
Cost_roc to Cost_london for all activities for all jobs in the update
table that have a referance starting with specific letters. The new
prices need to be obtained from the activities table.

Would be very gratefull for any help on this matter

Regards,

Ian Selby

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Ian Selby (ian.selby@.lojics.co.uk) writes:
> Hi all, I need to update some data in a table, based on some criteria.
> In this case we are talking about the stamping of a price against a job.
> The update table holds the jobs, and the update_details table holds the
> activities performed on each job and the cost for each activity. If i
> pull back this information using the following code
>...

Your question seems to have been left unanswered, and unfortunately I
cannot provide any answer to you. The reason for this, is that I cannot
understand how the values in the Cost_London and Cost_Roc column
maps to the rows in the first result set.

The standard recommendation for getting help with a query is to post:

o CREATE TABLE scripts of the tables involved. (It helps to include
PRIMARY KEY and FOREIGN KEY references.
o INSERT statements with sample data.
o The result you want given the sample data.

This makes it easier to understand what you after, and also it makes it
possible to post a tested solution.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

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

Giving a total on a column value change

I have the following data in a table
date StartTm ElapseTm shift Job# Op#
WorkCtr Qty1 Qty2
20051218 0030 0030 13 165990 225 9 .0000 0
20051218 0100 0030 13 165990 223 9 .0000 0
20051218 0130 0145 13 165990 200 9
26219.0000 28220
20051218 0315 0030 13 165990 300 9 .0000 0
20051218 0345 0115 13 165988 100 9 .0000 0
20051218 0500 0030 13 165900 300 9 .0000 0
20051218 0530 0130 13 165975 100 9 .0000 0
20051218 2300 0100 13 166436 300 9 .0000 0
20051218 2400 0030 13 165990 100 9 .0000 0
I need to know how to calculate the quantities based on totals of
successive jobs.
For example the first total for job 165990 would be 26219
the next total for job 165990 would be 0 for Qty1
the first total for job 165988 would be 0 for Qty1
so aggregated I would want to see
165988 0
165990 26219
165990 0
I need to be able to apply a identifier to these groups of successive
jobs by date and by WorkCtr.
I group the values by work_ctr, day, start_tm, job_no
Within that grouping when the job changes give a total.
I hope I am being clear. Thanks for your time in advance.I'm .
Why is the row with 26219 the first total for job 165990? It seems that
based on start time this row is the 3rd of 4 for this job. What is the
criteria you want to use for ordering the rows for a specific job.
When you say aggregated what do you mean? Do you just mean ordered? Or are
you intending to combine the rows somehow.
Also, I am by your last set of statements. what do you mean by
apply an identifier to the groups. And what does it mean when you say when
the job changes give a total?
If you can give the full version of your desired output based on the data
that you provide and answer by above confusion it will make it easier for us
.
Thanks
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"g3000" wrote:

> I have the following data in a table
> date StartTm ElapseTm shift Job# Op#
> WorkCtr Qty1 Qty2
> 20051218 0030 0030 13 165990 225 9 .0000 0
> 20051218 0100 0030 13 165990 223 9 .0000 0
> 20051218 0130 0145 13 165990 200 9
> 26219.0000 28220
> 20051218 0315 0030 13 165990 300 9 .0000 0
> 20051218 0345 0115 13 165988 100 9 .0000 0
> 20051218 0500 0030 13 165900 300 9 .0000 0
> 20051218 0530 0130 13 165975 100 9 .0000 0
> 20051218 2300 0100 13 166436 300 9 .0000 0
> 20051218 2400 0030 13 165990 100 9 .0000 0
> I need to know how to calculate the quantities based on totals of
> successive jobs.
> For example the first total for job 165990 would be 26219
> the next total for job 165990 would be 0 for Qty1
> the first total for job 165988 would be 0 for Qty1
> so aggregated I would want to see
> 165988 0
> 165990 26219
> 165990 0
> I need to be able to apply a identifier to these groups of successive
> jobs by date and by WorkCtr.
> I group the values by work_ctr, day, start_tm, job_no
> Within that grouping when the job changes give a total.
> I hope I am being clear. Thanks for your time in advance.
>|||Sorry, I actually solved the problem my issue was this
Date Job# Qty
20051201 165203 20
-- break here and total is 20 unique id
20051201 165202 15
-- break here and total is 20 unique
id
20051201 165203 10
20051201 165203 10
20051201 165203 10
20051202 165203 10
-- break here and total is
40. The above four records shoud have a unique group id
20051202 165222 05
blah blah blah
bottom line I have jobs that run and stop then start again
if the same job is started, stopped and restarted in succession that is
a unique group
if the next job is not the same as the first i needed a new unique
identifier and a total
I wanted to get totals for these groups but had no way to group those
by
just did a cursor to a table and looped through and had new colun in my
table
clear as mud?|||It's actually quite simple to solve if you don't let
the complexities of sql cloud the issue.Every block
gets an ascending unique integer.Then you can group
by this new integer.The convoluted sql comes in when
trying to derive a new integer for a new block.It's a rank
problem at most.
Ranking was implemented years ago in the RAC utility:)
Perhaps someone can wrap their head around sql 2005
ranking functions and solve it,if not I can post RAC solution:)
Check out RAC @.
www.rac4sql.net|||On 12 Jan 2006 12:40:25 -0800, g3000 wrote:

>Sorry, I actually solved the problem my issue was this
>Date Job# Qty
>20051201 165203 20
> -- break here and total is 20 unique id
>20051201 165202 15
> -- break here and total is 20 unique
>id
>20051201 165203 10
>20051201 165203 10
>20051201 165203 10
>20051202 165203 10
> -- break here and total is
>40. The above four records shoud have a unique group id
>20051202 165222 05
> blah blah blah
>bottom line I have jobs that run and stop then start again
>if the same job is started, stopped and restarted in succession that is
>a unique group
>if the next job is not the same as the first i needed a new unique
>identifier and a total
>I wanted to get totals for these groups but had no way to group those
>by
>just did a cursor to a table and looped through and had new colun in my
>table
>clear as mud?
Hi g3000,
You don't need a cursor for this. The following single query should
produce the same results. You might wish to compare this with your
current solution and retain the version that performs best or is easiest
to maintain (dfepending on what ranks top priority in your DB).
SELECT a.Job#, SUM(a.Qty)
FROM YourTable AS a
GROUP BY a.Job#, (SELECT COUNT(DISTINCT b.Job#)
FROM YourTable AS b
WHERE b."Date" <= a."Date")
Hugo Kornelis, SQL Server MVP|||thank you Hugo,
I will try that. Didnt think about that. Looks like what I
want. I am not familiar with ranking. So ill have to look into that
later.