SQL DBOwner

If you need to create a DBOwner User and Update all databases to be owned by DBOwner

CREATE LOGIN [DBOwner] 
       WITH PASSWORD=N'SomeReallyGoodPassword', 
       DEFAULT_DATABASE=[master], 
       CHECK_EXPIRATION=OFF, 
       CHECK_POLICY=ON;
 
GO
DENY CONNECT SQL TO [DBOwner];
GO
ALTER LOGIN [DBOwner] DISABLE;
GO

Once you create the DBOwner user this query will create a query to update the database owner to be DBOwner

SELECT suser_sname( owner_sid ) as [UserName], 
       IS_SRVROLEMEMBER('sysadmin', suser_sname( owner_sid )) as [IsSysAdmin],
       name, 
       database_id,
       'ALTER AUTHORIZATION ON DATABASE::[' + name + '] to DBOwner;' as Script
FROM sys.databases
WHERE database_id > 4
  AND name <> 'SSISDB'
http://databasehealth.com/server-overview/quick-scan-report/quick-scan-report-database-ownership/: SQL DBOwner

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *