Audit User Owned Objects
We are currently working on removing a large collection of legacy SQL / Windows login accounts. Although the users have left the company there accounts can still own objects on an instance which means removing them can be an issue and break things, ive linked to a few blog posts of possible issues below to watch out for.
Who Owns your Jobs – Tibor Karaszi
Steps to Drop an Orphan SQL Server User when it owns a Schema or Role – Jugal Shah
I’ve put together a script to try and find anything on an instance that could be owned by a user and list them out in a single table to make it easy to read / work from. Hopefully this will help others as much as its helping me.
EDIT – I’ve edited this script several times since the orginal posting, making improvements and corrections
/* Query to Audit User Owned Objects -- List all Databases owned by a user -- List all Agent Jobs owned by a user -- List all Packages owned by a user -- List all Scheams owned by a user -- List all Objects owned by a user -- Edit Added logic to test for if SQL version is lower than 2008 for SSIS packages Fixed variable issue Changed If logic from IF 2008 > stop to only check at SSIS package Use sys.databases to clean up working iwth 2005. 2008 2012 instances */ IF OBJECT_ID('tempdb..#ownerTable') IS NOT NULL DROP TABLE #ownerTable CREATE TABLE #ownerTable ( [Issue] VARCHAR(100) , [Database] VARCHAR(200) , [Object] VARCHAR(200) , [ObjectType] VARCHAR(200) , [Owner] VARCHAR(200) ) /* -- List all Non SA Database Owners ---------------------------------------------------------------------------------------------------------------------- */ INSERT INTO #ownerTable ( [Issue] , [Database] , [Owner] ) SELECT 'Database Owned by a User' , name AS 'Name' , SUSER_SNAME(owner_sid) AS 'Owner' FROM sys.databases WHERE SUSER_SNAME(owner_sid) <> 'sa'; /* -- List all Non SA Job Owners ---------------------------------------------------------------------------------------------------------------------- */ INSERT INTO #ownerTable ( [Issue] , [Database] , [Object] , [Owner] ) SELECT 'Agent Job Owned by a User' , 'msdb' , s.name AS 'Job Name' , l.name AS ' Owner' FROM msdb..sysjobs s LEFT JOIN master.sys.syslogins l ON s.owner_sid = l.sid WHERE l.name <> 'sa'; /* -- List all Non SA Package Owners ---------------------------------------------------------------------------------------------------------------------- */ DECLARE @v INT SET @v = CONVERT(INT, LEFT(CONVERT(VARCHAR(MAX), SERVERPROPERTY('ProductVersion')), CONVERT(INT, CHARINDEX('.', CONVERT(VARCHAR(MAX), SERVERPROPERTY('ProductVersion')))) - 1)) IF ( @v >= 10 ) BEGIN INSERT INTO #ownerTable ( [Issue] , [Database] , [Object] , [ObjectType] , [Owner] ) SELECT 'SSIS Packages Owned by a User' , 'msdb' , s.name AS 'object' , 'Maintenance Plan' , l.name AS 'Owner' FROM msdb..sysssispackages s LEFT JOIN master.sys.syslogins l ON s.ownersid = l.sid WHERE l.name <> 'sa' OR l.name IS NULL; END ELSE BEGIN INSERT INTO #ownerTable ( [Issue] , [Database] , [Object] , [ObjectType] , [Owner] ) SELECT 'SSIS Packages Owned by a User' , 'msdb' , s.name , 'Maintenance Plan' , l.name FROM [msdb].[dbo].[sysdtspackages90] AS s LEFT JOIN sysusers l ON s.ownersid = l.sid WHERE l.name <> 'sa' OR l.name IS NULL END /* -- List all Schemas owned by Users ---------------------------------------------------------------------------------------------------------------------- */ DECLARE @DB_NameSch VARCHAR(100) DECLARE @CommandSch NVARCHAR(MAX) DECLARE database_cursor CURSOR FOR SELECT name FROM sys.databases WHERE state_desc = 'ONLINE' AND user_access_desc = 'MULTI_USER' OPEN database_cursor FETCH NEXT FROM database_cursor INTO @DB_NameSch WHILE @@FETCH_STATUS = 0 BEGIN SELECT @CommandSch = 'USE [' + @DB_NameSch + '] SELECT ' + '''' + 'Schema Owned by a User' + '''' + ', ' + '''' + @DB_NameSch + '''' + ' as [DatabaseName], name as ''Schema'' , USER_NAME(principal_id) AS ''Owner'' FROM sys.schemas WHERE name NOT IN (''TargetServersRole'', ''SQLAgentUserRole'', ''SQLAgentReaderRole'', ''SQLAgentOperatorRole'', ''DatabaseMailUserRole'' , ''db_ssisadmin'', ''db_ssisltduser'', ''db_ssisoperator'', ''RSExecRole'') AND schema_id > 4 AND schema_id < 16384 AND principal_id <> 1 ' -- PRINT @CommandSch -- List all Non SA Package Owners INSERT INTO #ownerTable ( [Issue] , [Database] , [Object] , [Owner] ) EXEC sp_executesql @CommandSch FETCH NEXT FROM database_cursor INTO @DB_NameSch END CLOSE database_cursor DEALLOCATE database_cursor /* -- Objects Owned by User ---------------------------------------------------------------------------------------------------------------------- */ DECLARE @DB_NameObj VARCHAR(100) DECLARE @CommandObj NVARCHAR(MAX) DECLARE database_cursor CURSOR FOR SELECT name FROM sys.databases WHERE state_desc = 'ONLINE' AND user_access_desc = 'MULTI_USER' OPEN database_cursor FETCH NEXT FROM database_cursor INTO @DB_NameObj WHILE @@FETCH_STATUS = 0 BEGIN SELECT @CommandObj = 'USE [' + @DB_NameObj + '] ; WITH objects_cte AS ( SELECT o.name , o.type_desc , CASE WHEN o.principal_id IS NULL THEN s.principal_id ELSE o.principal_id END AS principal_id FROM sys.objects o INNER JOIN sys.schemas s ON o.schema_id = s.schema_id WHERE o.is_ms_shipped = 0 AND o.type IN ( ''U'', ''FN'', ''FS'', ''FT'', ''IF'', ''P'', ''PC'', ''TA'', ''TF'', ''TR'', ''V'' ) ) SELECT ''Object Owned by User'', DB_NAME() AS ''Database'', cte.name AS ''Object'', cte.type_desc AS ''ObjectType'', dp.name AS ''Owner'' FROM objects_cte cte INNER JOIN sys.database_principals dp ON cte.principal_id = dp.principal_id WHERE dp.name NOT IN ( ''dbo'', ''cdc'');' -- PRINT @CommandObj -- List all Non SA Package Owners INSERT INTO #ownerTable ( [Issue] , [Database] , [Object] , [ObjectType] , [Owner] ) EXEC sp_executesql @CommandObj FETCH NEXT FROM database_cursor INTO @DB_NameObj END CLOSE database_cursor DEALLOCATE database_cursor /* -- Show results ---------------------------------------------------------------------------------------------------------------------- */ SELECT * FROM #ownerTable; -- drop table DROP TABLE #ownerTable;
[…] my security audit, which i wrote about yesterday (https://sqlnotesfromtheunderground.wordpress.com/2014/06/18/query-to-find-all-dbs-schemas-jobs-object… ). I’ve found a couple of occasions a database will have a schema which is named the same as […]
Great script! Thanks!
Hi, hope it helped!
[…] i ran my trusty (or not so much) from Audit User Owned Objects i didnt get any results! most upsetting.. looking around a bit more, i found that it was due to a […]
Excellent, thank you
Very useful, thanks!