Making a user DBO for all databases on a server

Recently I had a need to assign a user DBO to all databases on a server.

I put this script together, it seems to do the trick.

declare @login varchar(255)='testuser'

declare @databasename varchar(255),
        @sql varchar(max)

declare xcn cursor fast_forward local for 
select
    a.name
from
    sys.sysdatabases a
where
    a.dbid>4
order by 
    a.name

open xcn

fetch next from xcn into @databasename

while @@FETCH_STATUS=0
begin
    exec ('use [' + @databasename + ']');

    set @sql='
    if not exists(
    select
        ''' + @databasename + ''',
        1
    from
    (
        SELECT 
            [name] as [user]
        FROM 
            [' + @databasename + '].sys.syslogins
    ) n
    where
        n.[User]=''' + @login + '''
    ) 
    begin
        print ''updating ' + @databasename + ''';
        use [' + @databasename + '];
        exec(''create user [' + @login + '] from login [' + @login + ']'');
    end;'
    +' if not exists(
    select
        ''' + @databasename + ''',
        1
    from
    (
        SELECT 
            USER_NAME(role_principal_id) AS [Role],
            USER_NAME(member_principal_id) AS [User]
        FROM 
            [' + @databasename + '].sys.database_role_members
        WHERE 
            USER_NAME(role_principal_id) IN (''db_owner'', ''db_accessadmin'')
    ) n
    where
        n.[User]=''' + @login + '''
    ) 
    begin
        print ''updating ' + @databasename + ''';
        use [' + @databasename + '];
        exec(''dbo.sp_addrolemember ''''db_owner'''', ''''' + @login + ''''''');
    end'

    --print @sql
    exec(@sql)

    fetch next from xcn into @databasename
end
close xcn

deallocate xcn

Be the first to comment

Leave a Reply