October 22, 2013 at 8:09 am
Dear all,
I am trying to find out if there is a way of finding out if a particular login in different servers owns a job instead of loging into the individual server and finding out please?
Thank you in advance!
October 22, 2013 at 8:21 am
You could run a query like this:
SELECT dbo.SQLAGENT_SUSER_SNAME(owner_sid)
, SUSER_SNAME(owner_sid) as OwnerName
, name as JobName
FROM dbo.sysjobs
with a where clause on the owner name that you are interested in. If you need to run it against multiple servers you could create a Central Management Server and then add the servers to a group and run the query against those servers.
October 22, 2013 at 8:31 am
You have to be logged in to the server that you want to query. Another option is to use login to 1 server and use linked servers or openrowset function to query the other servers. You'll have to use a query that looks more or less like the one bellow
select name as JobName
from msdb.dbo.sysjobs
where suser_sname(owner_sid) = 'Write Login name here'
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 22, 2013 at 8:35 am
Adi Cohn-120898 (10/22/2013)
You have to be logged in to the server that you want to query. Another option is to use login to 1 server and use linked servers or openrowset function to query the other servers. You'll have to use a query that looks more or less like the one bellow
select name as JobName
from msdb.dbo.sysjobs
where suser_sname(owner_sid) = 'Write Login name here'
Adi
That is why I recommended use a Central Management Server.
October 23, 2013 at 7:32 am
..or, if this happens a lot, and it's on SQL2008 and above, you could use Policy Based Management. 🙂
Easier solution would be to use Powershell.
[font="Courier New"]sqlmunkee[/font]
[font="Courier New"]Bringing joy and happiness via SQL Server since 1998[/font]
October 24, 2013 at 5:50 am
Thank you all for your recommendations.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply