Monday, April 22, 2013

MS SQL Server 2008: Add domain user to database owner list

1. Start - All Programs - Microsoft SQL Server 2008 R2 - Configuration Tools - SQL Server Configuration Manager
2. Right-click on database, select New Query, paste the following code, replace DOMAIN\user with real value, start script:

EXECUTE master.sys.sp_MSforeachdb ' 
    use [?]; 
    declare @user sysname = ''DOMAIN\user'' 
    if db_name() not in (''master'', ''model'', ''msdb'', ''tempdb'') 
    begin 
        if not exists ( 
            select null 
            from dbo.sysusers 
            where name = @user 
        ) 
        begin 
            print ''Adding '' + @user + '' to '' + db_name(); 
            exec sp_grantdbaccess @user, @user; 
        end; 
    print ''Granting '' + @user + '' access rights to '' + db_name(); 
    exec sp_addrolemember db_owner, @user; 
    end;
'

No comments:

Post a Comment