How to use ownersid column value of sysdtspackages

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

How to use ownersid column value of sysdtspackages

Author

Topic

MGouders
Starting Member

30 Posts

Posted - 2009-10-19 : 17:45:27

Does someone know which system table or system view can be used to link with the ownersid column of sysdtspackages90 to identify the creator of an SSIS package.

Example:
SELECT *
FROM msdb.dbo.sysusers
WHERE sid = (SELECT ownersid
FROM msdb.dbo.sysdtspackages90
WHERE name = 'SSIS_Package_Name')

The subquery returns a value.
But the main query does not.

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-10-19 : 17:52:55

You could try syslogins, but the sids should be the same between the two if the accounts aren't orphaned. If you aren't finding the user, then that might suggest the user doesn't exist anymore.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

MGouders
Starting Member

30 Posts

Posted - 2009-10-19 : 18:27:24

Thanks for the Info

Do you also happen to know, what can be done, if the account is orphaned?

Thanks

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-10-19 : 18:38:03

You can unorphan an account via sp_change_users_login. Check BOL for details.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

"Let's begin with the premise that everything you've done up until this point is wrong."

SreenivasBora
Posting Yak Master

164 Posts

Posted - 2009-10-20 : 10:32:11

SELECT [dts].[name] [package_name], SUSER_SNAME([dts].[ownersid]) [package_owner],
(select foldername from sysdtspackagefolders90 where folderid = dts.folderid) Foldername, createdate
FROM [msdb].[dbo].[sysdtspackages90] [dts]
ORDER BY [package_name]

Run the Query and see....

Regards
SRI

With Regards
BSR

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources