Null values: lock

Showing posts with label lock. Show all posts

Monday, March 26, 2012

Rights to execute jobs?

Hello!
We're trying to lock down our environments (dev, qa, uat). One requirement
is that a group needs to not be an admin but still be able to execute any
job. I've looked and looked, but I haven't yet been able to find a good
resource for job execution permissions. I know that a job owner can execute
a
job, but our standard is to make all jobs owned by SA. So, can somebody
outline what permissions are required in order for a non-sysadmin to run a
job not owned by themselves? Is that possible? It occurs to me that I may be
able to do this by making a stored proc to execute a passed-in job name.
Since procs execute anything inside it (only permissions needed are to the
proc itself), this may work. I'd like to avoid this, though, and let them ru
n
through EM.
Thanks for the help!Brian,
Are you running SQL2000 or SQL2005? It looks like 2000 because you mentioned
EM. We, just moving up to SQL2005, have a similar problem and have created
an SQL login to run jobs. A Windows group cannot own a job and so, as you
say, cannot create jobs with other owners unless it is a sysadmin. If
running with a created SQL Login seems okay to you I can detail the
permissions that we are giving.
Chris
"Brian Laws" <BrianLaws@.discussions.microsoft.com> wrote in message
news:AA330618-DB8C-48E9-A43B-BCD1C9053EF1@.microsoft.com...
> Hello!
> We're trying to lock down our environments (dev, qa, uat). One requirement
> is that a group needs to not be an admin but still be able to execute any
> job. I've looked and looked, but I haven't yet been able to find a good
> resource for job execution permissions. I know that a job owner can
> execute a
> job, but our standard is to make all jobs owned by SA. So, can somebody
> outline what permissions are required in order for a non-sysadmin to run a
> job not owned by themselves? Is that possible? It occurs to me that I may
> be
> able to do this by making a stored proc to execute a passed-in job name.
> Since procs execute anything inside it (only permissions needed are to the
> proc itself), this may work. I'd like to avoid this, though, and let them
> run
> through EM.
> Thanks for the help!|||Yes, SQL2000. In SQL 2005 there are the new roles in the MSDB database which
will grant rights, but I need something similar in 2000. Since we're using
Windows Authentication, we can't just have a shared SQL login. I could creat
e
a new account in common, but then there's no accountability. Plus, the user
would then have to be the owner of the job, which breaks away from standards
and makes development different from production.
"Chris Wood" wrote:

> Brian,
> Are you running SQL2000 or SQL2005? It looks like 2000 because you mention
ed
> EM. We, just moving up to SQL2005, have a similar problem and have created
> an SQL login to run jobs. A Windows group cannot own a job and so, as you
> say, cannot create jobs with other owners unless it is a sysadmin. If
> running with a created SQL Login seems okay to you I can detail the
> permissions that we are giving.
> Chris
> "Brian Laws" <BrianLaws@.discussions.microsoft.com> wrote in message
> news:AA330618-DB8C-48E9-A43B-BCD1C9053EF1@.microsoft.com...
>
>|||Brian,
One way to let users in a DEV environment be able to start jobs is to
trigger them through alerts. But I do not believe you can control who can
raise the alert, so it could be anyone on the server.
You can add an alert something like this:
EXEC msdb.dbo.sp_add_alert @.name=N'Start My #1 Job',
@.message_id=50101,
@.severity=0,
@.enabled=1,
@.database_name=N'FavoriteDB',
@.job_name=N'My #1 Job'
Of course, you need to define the messages (e.g. 50101) in sysmessages and
so forth, but the user only has to do a RAISERROR with the proper message
number, and the alert will start the job. You can put that in a procedure,
or whatever works best for you.
If this is good enough for you, it leaves your job definitions in
development and production just alike. Only, in production you would
probably not define the alerts.
RLF
"Brian Laws" <BrianLaws@.discussions.microsoft.com> wrote in message
news:E24E82B3-531F-4FA7-B91B-69783DD7EE7B@.microsoft.com...[vbcol=seagreen]
> Yes, SQL2000. In SQL 2005 there are the new roles in the MSDB database
> which
> will grant rights, but I need something similar in 2000. Since we're using
> Windows Authentication, we can't just have a shared SQL login. I could
> create
> a new account in common, but then there's no accountability. Plus, the
> user
> would then have to be the owner of the job, which breaks away from
> standards
> and makes development different from production.
> "Chris Wood" wrote:
>|||This is a method that we use in our SQL2005 servers so that lower level
users can get admin type jobs going. As Russell mentions you need a step in
your job that performs a RAISERROR with a high enough severity level and
with no logging specified. The alert then fires off your job that runs under
a higher user.
Chris
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23wPjDjq3HHA.4680@.TK2MSFTNGP06.phx.gbl...
> Brian,
> One way to let users in a DEV environment be able to start jobs is to
> trigger them through alerts. But I do not believe you can control who can
> raise the alert, so it could be anyone on the server.
> You can add an alert something like this:
> EXEC msdb.dbo.sp_add_alert @.name=N'Start My #1 Job',
> @.message_id=50101,
> @.severity=0,
> @.enabled=1,
> @.database_name=N'FavoriteDB',
> @.job_name=N'My #1 Job'
> Of course, you need to define the messages (e.g. 50101) in sysmessages and
> so forth, but the user only has to do a RAISERROR with the proper message
> number, and the alert will start the job. You can put that in a procedure,
> or whatever works best for you.
> If this is good enough for you, it leaves your job definitions in
> development and production just alike. Only, in production you would
> probably not define the alerts.
> RLF
> "Brian Laws" <BrianLaws@.discussions.microsoft.com> wrote in message
> news:E24E82B3-531F-4FA7-B91B-69783DD7EE7B@.microsoft.com...
>|||Interesting method. Thanks. I can put that in a stored procedure to make lif
e
easier for people.
Does anyone know if the rules surrounding stored procedure permissions apply
to calling sp_start_job as well? If a user has permissions to run a stored
proc, than anything inside it can be done regardless of whether the user was
granted that access. Does this apply to a proc calling sp_start_job so that
I
can have it kick off a job not owned by that user?
Thanks for your help!
Brian
"Russell Fields" wrote:

> Brian,
> One way to let users in a DEV environment be able to start jobs is to
> trigger them through alerts. But I do not believe you can control who can
> raise the alert, so it could be anyone on the server.
> You can add an alert something like this:
> EXEC msdb.dbo.sp_add_alert @.name=N'Start My #1 Job',
> @.message_id=50101,
> @.severity=0,
> @.enabled=1,
> @.database_name=N'FavoriteDB',
> @.job_name=N'My #1 Job'
> Of course, you need to define the messages (e.g. 50101) in sysmessages and
> so forth, but the user only has to do a RAISERROR with the proper message
> number, and the alert will start the job. You can put that in a procedure,
> or whatever works best for you.
> If this is good enough for you, it leaves your job definitions in
> development and production just alike. Only, in production you would
> probably not define the alerts.
> RLF
> "Brian Laws" <BrianLaws@.discussions.microsoft.com> wrote in message
> news:E24E82B3-531F-4FA7-B91B-69783DD7EE7B@.microsoft.com...
>
>|||The rules still apply when executing sp_start_job.
-Sue
On Thu, 16 Aug 2007 18:17:43 -0700, Brian Laws
<BrianLaws@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Interesting method. Thanks. I can put that in a stored procedure to make li
fe
>easier for people.
>Does anyone know if the rules surrounding stored procedure permissions appl
y
>to calling sp_start_job as well? If a user has permissions to run a stored
>proc, than anything inside it can be done regardless of whether the user wa
s
>granted that access. Does this apply to a proc calling sp_start_job so that
I
>can have it kick off a job not owned by that user?
>Thanks for your help!
>Brian
>"Russell Fields" wrote:
>sql

Wednesday, March 21, 2012

Rewriting a query causing lock escalation

All,
I've identified a query that is causing deadlocks in our database. The
SELECT portion of this query seems to be causing lock escalation - causing
the whole RECORDS table to lock - and causing other processes to deadlock.
I'd like to somehow rewrite this query to decrease the lock escalation. Does
anyone have any ideas as to how I can do this? This query is part of a
stored procedure that gets called via an insert trigger on a different
table. All fields are integers. 5 variables are inputs to this query -
@.P_TID, @.P_SID, @.T_TID, @.T_SID, @.E_ID.
I was thinking of breaking up the query into smaller versions ... but not
sure where to start.
I should add that we added option (maxdop 1) to force this query to only run
on one thread - it seems to reduce the deadlocks - but not eliminate them.
Thanks in advance ... SS
---
option (maxdop 1)
INSERT INTO RECORDS
(
P_TID
,P_SID
,T_TID
,T_SID
,E_ID
,PCOUNT
)
SELECT
p1.P_TID
,p1.P_SID
,p2.T_TID
,p2.T_SID
,@.E_ID
,p1.PCOUNT*p2.PCOUNT
FROM
(RECORDS p1 WITH (ROWLOCK)
INNER JOIN RECORDS p2 WITH (ROWLOCK)
ON p1.T_TID=@.P_TID
AND p1.T_SID=@.P_SID
AND p2.P_TID=@.T_TID
AND p2.P_SID=@.T_SID
) LEFT JOIN RECORDS p3 WITH (ROWLOCK)
ON p3.P_TID=p1.P_TID
AND p3.P_SID=p1.P_SID
AND p3.T_TID=p2.T_TID
AND p3.T_SID=p2.T_SID
AND p3.E_ID=@.E_ID
WHERE
p1.E_ID IN (0, @.E_ID)
AND p2.E_ID IN (0, @.E_ID)
AND p3.E_ID IS NULL
option (maxdop 1)Hi Steph
How many rows are in Records with E_ID IN (0, @.E_ID), you may want to move
them into a temporary table. Also/alternatively check the query execution
plan to see if adding index will help, you may want to try the Index tuning
wizard to see if it comes up with anything.
John
"Steph" wrote:
> All,
> I've identified a query that is causing deadlocks in our database. The
> SELECT portion of this query seems to be causing lock escalation - causing
> the whole RECORDS table to lock - and causing other processes to deadlock.
> I'd like to somehow rewrite this query to decrease the lock escalation. Does
> anyone have any ideas as to how I can do this? This query is part of a
> stored procedure that gets called via an insert trigger on a different
> table. All fields are integers. 5 variables are inputs to this query -
> @.P_TID, @.P_SID, @.T_TID, @.T_SID, @.E_ID.
> I was thinking of breaking up the query into smaller versions ... but not
> sure where to start.
> I should add that we added option (maxdop 1) to force this query to only run
> on one thread - it seems to reduce the deadlocks - but not eliminate them.
> Thanks in advance ... SS
> ---
> option (maxdop 1)
> INSERT INTO RECORDS
> (
> P_TID
> ,P_SID
> ,T_TID
> ,T_SID
> ,E_ID
> ,PCOUNT
> )
> SELECT
> p1.P_TID
> ,p1.P_SID
> ,p2.T_TID
> ,p2.T_SID
> ,@.E_ID
> ,p1.PCOUNT*p2.PCOUNT
> FROM
> (RECORDS p1 WITH (ROWLOCK)
> INNER JOIN RECORDS p2 WITH (ROWLOCK)
> ON p1.T_TID=@.P_TID
> AND p1.T_SID=@.P_SID
> AND p2.P_TID=@.T_TID
> AND p2.P_SID=@.T_SID
> ) LEFT JOIN RECORDS p3 WITH (ROWLOCK)
> ON p3.P_TID=p1.P_TID
> AND p3.P_SID=p1.P_SID
> AND p3.T_TID=p2.T_TID
> AND p3.T_SID=p2.T_SID
> AND p3.E_ID=@.E_ID
> WHERE
> p1.E_ID IN (0, @.E_ID)
> AND p2.E_ID IN (0, @.E_ID)
> AND p3.E_ID IS NULL
> option (maxdop 1)
>
>|||John - thanks for the suggestion. I did look at the execution plans - and
saw where some bottlenecks could be. The Index Tuning Wizard suggested
putting an index on a column that either has the values 0, 1, 2 - so not
sure how useful the index would be ...
I'm considering doing the select - putting the results into a table variable
then inserting the values from the table variable. Don't want to add any
temp tables now - don't want to add more locks on tempdb.
Thanks for suggestions ...
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:441B4D04-2EF2-4A7F-B131-05B04A63A63A@.microsoft.com...
> Hi Steph
> How many rows are in Records with E_ID IN (0, @.E_ID), you may want to move
> them into a temporary table. Also/alternatively check the query execution
> plan to see if adding index will help, you may want to try the Index
tuning
> wizard to see if it comes up with anything.
> John
>
> "Steph" wrote:
> > All,
> >
> > I've identified a query that is causing deadlocks in our database. The
> > SELECT portion of this query seems to be causing lock escalation -
causing
> > the whole RECORDS table to lock - and causing other processes to
deadlock.
> >
> > I'd like to somehow rewrite this query to decrease the lock escalation.
Does
> > anyone have any ideas as to how I can do this? This query is part of a
> > stored procedure that gets called via an insert trigger on a different
> > table. All fields are integers. 5 variables are inputs to this query -
> > @.P_TID, @.P_SID, @.T_TID, @.T_SID, @.E_ID.
> >
> > I was thinking of breaking up the query into smaller versions ... but
not
> > sure where to start.
> >
> > I should add that we added option (maxdop 1) to force this query to only
run
> > on one thread - it seems to reduce the deadlocks - but not eliminate
them.
> >
> > Thanks in advance ... SS
> >
> > ---
> >
> > option (maxdop 1)
> >
> > INSERT INTO RECORDS
> > (
> > P_TID
> > ,P_SID
> > ,T_TID
> > ,T_SID
> > ,E_ID
> > ,PCOUNT
> > )
> > SELECT
> > p1.P_TID
> > ,p1.P_SID
> > ,p2.T_TID
> > ,p2.T_SID
> > ,@.E_ID
> > ,p1.PCOUNT*p2.PCOUNT
> > FROM
> > (RECORDS p1 WITH (ROWLOCK)
> > INNER JOIN RECORDS p2 WITH (ROWLOCK)
> > ON p1.T_TID=@.P_TID
> > AND p1.T_SID=@.P_SID
> > AND p2.P_TID=@.T_TID
> > AND p2.P_SID=@.T_SID
> > ) LEFT JOIN RECORDS p3 WITH (ROWLOCK)
> > ON p3.P_TID=p1.P_TID
> > AND p3.P_SID=p1.P_SID
> > AND p3.T_TID=p2.T_TID
> > AND p3.T_SID=p2.T_SID
> > AND p3.E_ID=@.E_ID
> > WHERE
> > p1.E_ID IN (0, @.E_ID)
> > AND p2.E_ID IN (0, @.E_ID)
> > AND p3.E_ID IS NULL
> > option (maxdop 1)
> >
> >
> >|||Hi
If you have a significant number of rows look at using a temporary table
rather than a table variable.
John
"Steph" wrote:
> John - thanks for the suggestion. I did look at the execution plans - and
> saw where some bottlenecks could be. The Index Tuning Wizard suggested
> putting an index on a column that either has the values 0, 1, 2 - so not
> sure how useful the index would be ...
> I'm considering doing the select - putting the results into a table variable
> then inserting the values from the table variable. Don't want to add any
> temp tables now - don't want to add more locks on tempdb.
> Thanks for suggestions ...
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:441B4D04-2EF2-4A7F-B131-05B04A63A63A@.microsoft.com...
> > Hi Steph
> >
> > How many rows are in Records with E_ID IN (0, @.E_ID), you may want to move
> > them into a temporary table. Also/alternatively check the query execution
> > plan to see if adding index will help, you may want to try the Index
> tuning
> > wizard to see if it comes up with anything.
> >
> > John
> >
> >
> > "Steph" wrote:
> >
> > > All,
> > >
> > > I've identified a query that is causing deadlocks in our database. The
> > > SELECT portion of this query seems to be causing lock escalation -
> causing
> > > the whole RECORDS table to lock - and causing other processes to
> deadlock.
> > >
> > > I'd like to somehow rewrite this query to decrease the lock escalation.
> Does
> > > anyone have any ideas as to how I can do this? This query is part of a
> > > stored procedure that gets called via an insert trigger on a different
> > > table. All fields are integers. 5 variables are inputs to this query -
> > > @.P_TID, @.P_SID, @.T_TID, @.T_SID, @.E_ID.
> > >
> > > I was thinking of breaking up the query into smaller versions ... but
> not
> > > sure where to start.
> > >
> > > I should add that we added option (maxdop 1) to force this query to only
> run
> > > on one thread - it seems to reduce the deadlocks - but not eliminate
> them.
> > >
> > > Thanks in advance ... SS
> > >
> > > ---
> > >
> > > option (maxdop 1)
> > >
> > > INSERT INTO RECORDS
> > > (
> > > P_TID
> > > ,P_SID
> > > ,T_TID
> > > ,T_SID
> > > ,E_ID
> > > ,PCOUNT
> > > )
> > > SELECT
> > > p1.P_TID
> > > ,p1.P_SID
> > > ,p2.T_TID
> > > ,p2.T_SID
> > > ,@.E_ID
> > > ,p1.PCOUNT*p2.PCOUNT
> > > FROM
> > > (RECORDS p1 WITH (ROWLOCK)
> > > INNER JOIN RECORDS p2 WITH (ROWLOCK)
> > > ON p1.T_TID=@.P_TID
> > > AND p1.T_SID=@.P_SID
> > > AND p2.P_TID=@.T_TID
> > > AND p2.P_SID=@.T_SID
> > > ) LEFT JOIN RECORDS p3 WITH (ROWLOCK)
> > > ON p3.P_TID=p1.P_TID
> > > AND p3.P_SID=p1.P_SID
> > > AND p3.T_TID=p2.T_TID
> > > AND p3.T_SID=p2.T_SID
> > > AND p3.E_ID=@.E_ID
> > > WHERE
> > > p1.E_ID IN (0, @.E_ID)
> > > AND p2.E_ID IN (0, @.E_ID)
> > > AND p3.E_ID IS NULL
> > > option (maxdop 1)
> > >
> > >
> > >
>
>|||Hi
If you are seeing contention on tempdb e.g. lock timeouts on database id 2
check out http://support.microsoft.com/default.aspx?scid=kb;en-us;328551
John
"Steph" wrote:
> John - thanks for the suggestion. I did look at the execution plans - and
> saw where some bottlenecks could be. The Index Tuning Wizard suggested
> putting an index on a column that either has the values 0, 1, 2 - so not
> sure how useful the index would be ...
> I'm considering doing the select - putting the results into a table variable
> then inserting the values from the table variable. Don't want to add any
> temp tables now - don't want to add more locks on tempdb.
> Thanks for suggestions ...
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:441B4D04-2EF2-4A7F-B131-05B04A63A63A@.microsoft.com...
> > Hi Steph
> >
> > How many rows are in Records with E_ID IN (0, @.E_ID), you may want to move
> > them into a temporary table. Also/alternatively check the query execution
> > plan to see if adding index will help, you may want to try the Index
> tuning
> > wizard to see if it comes up with anything.
> >
> > John
> >
> >
> > "Steph" wrote:
> >
> > > All,
> > >
> > > I've identified a query that is causing deadlocks in our database. The
> > > SELECT portion of this query seems to be causing lock escalation -
> causing
> > > the whole RECORDS table to lock - and causing other processes to
> deadlock.
> > >
> > > I'd like to somehow rewrite this query to decrease the lock escalation.
> Does
> > > anyone have any ideas as to how I can do this? This query is part of a
> > > stored procedure that gets called via an insert trigger on a different
> > > table. All fields are integers. 5 variables are inputs to this query -
> > > @.P_TID, @.P_SID, @.T_TID, @.T_SID, @.E_ID.
> > >
> > > I was thinking of breaking up the query into smaller versions ... but
> not
> > > sure where to start.
> > >
> > > I should add that we added option (maxdop 1) to force this query to only
> run
> > > on one thread - it seems to reduce the deadlocks - but not eliminate
> them.
> > >
> > > Thanks in advance ... SS
> > >
> > > ---
> > >
> > > option (maxdop 1)
> > >
> > > INSERT INTO RECORDS
> > > (
> > > P_TID
> > > ,P_SID
> > > ,T_TID
> > > ,T_SID
> > > ,E_ID
> > > ,PCOUNT
> > > )
> > > SELECT
> > > p1.P_TID
> > > ,p1.P_SID
> > > ,p2.T_TID
> > > ,p2.T_SID
> > > ,@.E_ID
> > > ,p1.PCOUNT*p2.PCOUNT
> > > FROM
> > > (RECORDS p1 WITH (ROWLOCK)
> > > INNER JOIN RECORDS p2 WITH (ROWLOCK)
> > > ON p1.T_TID=@.P_TID
> > > AND p1.T_SID=@.P_SID
> > > AND p2.P_TID=@.T_TID
> > > AND p2.P_SID=@.T_SID
> > > ) LEFT JOIN RECORDS p3 WITH (ROWLOCK)
> > > ON p3.P_TID=p1.P_TID
> > > AND p3.P_SID=p1.P_SID
> > > AND p3.T_TID=p2.T_TID
> > > AND p3.T_SID=p2.T_SID
> > > AND p3.E_ID=@.E_ID
> > > WHERE
> > > p1.E_ID IN (0, @.E_ID)
> > > AND p2.E_ID IN (0, @.E_ID)
> > > AND p3.E_ID IS NULL
> > > option (maxdop 1)
> > >
> > >
> > >
>
>

Monday, March 26, 2012

Rights to execute jobs?

Wednesday, March 21, 2012

Rewriting a query causing lock escalation

Rewriting a query causing lock escalation

Rewriting a query causing lock escalation

Null values

Blog Archive

About Me