Script to Change the db owner of all databases

I was in need of a script to change the owner of all the database in a server. I checked in the internet but couldnt find any thing so I made this by myself :)

DECLARE curDBNames CURSOR READ_ONLY FOR  SELECT name FROM sys.databases where

name NOT IN('master', 'model', 'msdb', 'tempdb','resource') and state = 0

DECLARE @dbName as varchar(1000)

DECLARE @Sql as varchar(1000)

declare @dbOwnerName as varchar(100)

set @dbOwnerName = 'sa'

OPEN curDBNames

      --PRINT @@ROWCOUNT           

      FETCH NEXT FROM curDBNames INTO @dbName

      WHILE @@FETCH_STATUS = 0

      BEGIN

     

            set @Sql = 'use [' + @dbName + ']'

        exec (@Sql)                      

        set @Sql = 'sp_changedbowner ' + @dbOwnerName

        exec (@Sql)

            FETCH NEXT FROM curDBNames INTO @dbName

      END  

      CLOSE curDBNames

      DEALLOCATE curDBNames
Hope you like the script. Let me know if something doesnt work. As usual pls double check before you try this in your production environment.

SQL Server Training Videos

This website is having some nice training videos classified on skill level.

Security loop Hole in "Security Admin " Role

Today I found out one interesting article, which describes the loop hole in security admin role, which has an ability to elivate an user to "sys admin". Please find the article here.

How to Grant Access to a Snap Shot

If you try to directly grant access to a snap shot you will get the below error stating that the database is in read only mode.


Msg 3906, Level 16, State 1, Line 1
Failed to update database "" because the database is read-only.
Msg 3906, Level 16, State 1, Procedure sp_MSadduser_implicit_ntlogin, Line 39
Failed to update database "" because the database is read-only


The reason is that the snapshots are in read only mode due to which we cannot make changes to add or remove a user.


If its a windows user then create a login in both the primary and mirror server. Then grant access to this login to the primary database, which will be replicated to the mirror.

If its a SQL Server login then its a bit tricky as the SIDs come into picture. You have to create a login in the primary then script it out with the SID using sp_help_revlogin script and then run the script in the mirror. Then grant the required access in the primary, which inturn will get replicated and the required user will have read access in the mirror and the snap shot.


Thats it for the day folks. Hope to I bump into some similar kind of problem and find a solution and waste google's page by blogging it here again. Until then its me signing off from you. Happy administrating & happy sunday cya..





With Smiles
Santhosh