August 5, 2015 at 4:36 pm
We're looking to rectify a situation where we can't delete the AD accounts of ex-employees, since they own objects in the database. The only 2 places I know of at this point where an owner can be identified, is 1) for the database, and 2) for each individual SQL Agent job.
Are there any other locations I should be looking?
Thanks,
--=Chuck
August 5, 2015 at 4:40 pm
They can own a schema as well.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 5, 2015 at 4:43 pm
SSIS/DTS packages. Including maintenance plans. This is harder to change.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 5, 2015 at 4:48 pm
WayneS (8/5/2015)
SSIS/DTS packages. Including maintenance plans. This is harder to change.
It's pretty easy to change. It's just a script.
If you try changing it otherwise, then yes it can become annoying.
and
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 7, 2015 at 3:29 pm
So, I've been looking through my databases, and I was unable to find any SSIS packages until today. But now I'm a bit confused. I have been using the terminology "Who owns the the SQL Agent Jobs" using the following query:
select s.name as jobname, l.name as owner
from msdb..sysjobs s
left join master.sys.syslogins l on s.owner_sid = l.sid
Now I'm using the following to define "Who owns SSIS packages":
select suser_sname(ownersid), t.*
from msdb.dbo.sysssispackages t
But those links I followed in the post above indicate that these "SSIS packages" are also synonymous with "SSIS jobs", and so now I'm a bit confused (although they're not included in any jobs defined in the Agent). Where should I be looking for these SSIS packages? I don't want to tinker with the owner until I at least know how these got into the database. I searched through all of the schemas' sysobjects tables and couldn't come up with the database in which these are installed.
In looking up definitions of SSIS packages, I'm getting more about what they are ("they help in transferring data") and less about how they're installed, from what tool, etc.
--=Chuck
--=Chuck
August 7, 2015 at 3:56 pm
chuck.forbes (8/7/2015)
So, I've been looking through my databases, and I was unable to find any SSIS packages until today. But now I'm a bit confused. I have been using the terminology "Who owns the the SQL Agent Jobs" using the following query:select s.name as jobname, l.name as owner
from msdb..sysjobs s
left join master.sys.syslogins l on s.owner_sid = l.sid
Now I'm using the following to define "Who owns SSIS packages":
select suser_sname(ownersid), t.*
from msdb.dbo.sysssispackages t
But those links I followed in the post above indicate that these "SSIS packages" are also synonymous with "SSIS jobs", and so now I'm a bit confused (although they're not included in any jobs defined in the Agent). Where should I be looking for these SSIS packages? I don't want to tinker with the owner until I at least know how these got into the database. I searched through all of the schemas' sysobjects tables and couldn't come up with the database in which these are installed.
In looking up definitions of SSIS packages, I'm getting more about what they are ("they help in transferring data") and less about how they're installed, from what tool, etc.
--=Chuck
--=Chuck
Often times ssis packages are tied to an agent job. The owner of the package will most of the time become the owner of the job. You really should change it in both places if there is a package and if that package is in an agent job.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 7, 2015 at 4:26 pm
And so if I examine every Agent job, and none of them list a step which executes *.dstx file that resembles the "name" column from:
select suser_sname(ownersid), t.*
from msdb.dbo.sysssispackages t
Should I assume that there once was a job which referenced the package, and that the packages aren't cleaned up when a job is deleted?
--=Chuck
August 7, 2015 at 5:03 pm
chuck.forbes (8/7/2015)
And so if I examine every Agent job, and none of them list a step which executes *.dstx file that resembles the "name" column from:select suser_sname(ownersid), t.*
from msdb.dbo.sysssispackages t
Should I assume that there once was a job which referenced the package, and that the packages aren't cleaned up when a job is deleted?
--=Chuck
I would say that is probably a safe assumption.
It could also be possible that the package is called from a different server.
I might pull the package down from the database and verify the package. See what it does and if it even works anymore.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply