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
Leave a Reply