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:
Post a Comment