Scenario: DBA1 left the company and never removed him/herself as the owner of databases all over the company. You are DBA2 and just got hired to replace DBA1. They plan to disable DBA1's account tomorrow.
You can either walk through every database on every server or connect to each server, choose your desired new owner login (in this case I choose SA) and run this script to save the day.
In later version of SQL Server you can run this against several instances at one time.
enjoy.
E Hendrix
EXEC sp_MSForEachDB 'EXEC sp_helpdb ?' ----I'd save this output to a text file somewhere for safe keeping EXEC sp_MSForEachDB 'Declare @name varchar(100) select @name = ''?'' PRINT @name IF db_id(@name) > 4 BEGIN USE ? EXEC dbo.sp_changedbowner @loginame = ''sa'', @map = false END'
2017-01-30 (first published: 2017-01-26)
17,821 reads
Have you ever wanted to run a query across every database on a server with the convenience of a stored procedure? If so, Microsoft provided a stored procedure to do so. It’s unreliable, outdated, and somewhat obfuscated, though. Let’s improve on it!
2016-04-15 (first published: 2014-12-01)
16,702 reads
2008-08-06 (first published: 2008-06-16)
2,523 reads
n a database I currently support, the application makes use of db_datareader and db_datawriter to give permissions to the tables. In a different database, it's even worse as the application uses db_owner. We're adding new tables to both databases and we don't want the application to have access to these tables. How can I deal with the use of these three roles?
2013-04-02
3,632 reads
This Script can be used for searching a Table in SQL server 2005
2009-08-04 (first published: 2009-07-18)
1,078 reads
