Want to search the sysobjects tables across all databases? This little bit of logic should help. Sure; it’s not hard to write a query across all databases… pffft… kid stuff, but it’s nice to have something to get you going if you need it. Save you a few keystrokes. Ok; so what this does is builds a list of queries in a #logic_table, and then runs the results into a #results table.
In this example I’m looking for a few older objects floating around so I can concatenate a query to revoke permissions from them. As you can see below; they are the RTblDMProps, mswebtask, and dtproperties objects.
As you can see; I’ve already confirmed the objects exist so I can write some logic to revoke the permissions. I’ve got maybe a couple hundred databases on this box so it’s nice to get a tidy little list with all the code in the temp tables so I can tweak, or expand on it if needed.
use master; set nocount on -- basic search across all databases be sure to repalce MyObject with the object you're searching for. select 'use [' + upper([name]) + ']; select upper(db_name()), [name], [xtype] from [' + name + ']..sysobjects where [name] in (''MyObject'');' from sys.databases where [name] not in ('tempdb') -- the remaining logic will produce a script on removing rights from the desired object(s). if object_id('tempdb..#logic_table') is not null drop table #logic_table create table #logic_table ([logic] varchar(max)) insert into #logic_table ([logic]) select 'use [' + upper([name]) + ']; select upper(db_name()), [name], [xtype] from [' + name + ']..sysobjects where [name] in (''MyObject1'', ''MyObject2'', ''MyObject3'');' from master.sys.databases where database_id <> 2 if object_id('tempdb..#results') is not null drop table #results create table #results ([lineid] int identity(1,1), [database] varchar(255), [name] varchar(255), [xtype] varchar(2)) declare @sql varchar(max) set @sql = '' select @sql = @sql + [logic] from #logic_table insert into #results ([database], [name], [xtype]) exec (@sql) select * from #results where [database] is not null -- build revoke statement select 'use [' + [database] + ']; revoke select, insert, update, delete, references on ' + [name] + ' to public;' from #results
Categories: Agent Jobs, Alerts, AlwaysOn, Automation, Automations, Backup, Delete, ELI5, Error Solutions, Failover, Failover Cluster, Featured, Forensics & Auditing, High Availability, How To, Installations, Job Steps, Jobs, Long Scripts, Management Studio, Mirroring, Quick Fix, Scripts, SQL Stuff, Top, TSQL