Friday, April 29, 2016

Best update database owner tools

In my never ending quest to automate the setting of DB owners here is the best script yet. It assumes there is a user called [Domain User]/[Server name]Server running the server.

USE Master
GO

DECLARE @userName nvarchar(50);
DECLARE @userNamePostfix nvarchar(10) = 'Server'; --'Admin';

IF  CHARINDEX('\', @@SERVERNAME, 0) > 0
SET @userName = '[Domain User]\' +  SUBSTRING(@@SERVERNAME, 0, CHARINDEX('\', @@SERVERNAME, 0)) + @userNamePostfix ;
ELSE
SET @userName = '[Domain User]\' +  @@SERVERNAME + @userNamePostfix;

--DECLARE @userName nvarchar(50) = '[Domain User]\' +  SUBSTRING(@@SERVERNAME, 0, CHARINDEX('\', @@SERVERNAME, 0)) + 'Admin';
-- Use this section to override if the pattern does not work
--SET @userName = 'override User';

PRINT @userName;

--Require the user to exist and be a sysadmin
IF EXISTS(SELECT sid FROM sys.syslogins WHERE name = @userName AND sysAdmin = 1)
BEGIN

--Should be modified to ignore DBs which are not assigned to a defaulte value
DECLARE @prefix  nvarchar(200) = 'IF ''?'' NOT IN (SELECT name from sys.databases WHERE owner_sid = SUSER_ID(''' + @userName + ''') OR DB_NAME(database_id) IN (''master'', ''msdb'',''model'', ''tempdb'', ''distribution'')) BEGIN  ';
DECLARE @postfix  nvarchar(50) = ' END';
DECLARE @cmd  nvarchar(512);

SET @cmd =  @prefix + 'PRINT ''?''; USE ?  exec sp_changedbowner @loginame=''' + @userName + ''' ' + @postfix;
PRINT @cmd;
EXEC master.sys.sp_MSforeachdb  @command1 = @cmd;
PRINT 'Successfully set DB owner to ' + @userName;
END
ELSE
BEGIN
  PRINT @userName + ' NOT found';
END

No comments: