patternsqlMinor

Assign db_owner to all users at once

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

oncealldb_ownerusersassign

Problem

I need to assign db_owner to all users within an instance. There are a few hundred logins and same number of databases within the instance. I would like each user to get db_owner within the database where that user exists. I know how to do it for individual users, but I do not know how it's done for all of them at once.

I am well aware that it is not a good idea to give db_owner to everyone, but...we are installing a very specific application. Every users who wants to use this application will need db_owner. We already tried walkarounds, but the aplicaton is rather primitive in nature so until application code is changed we will have to tolerate insane number of db_owners. There is no sensitive data on this particular instance anyway so we have management approval.

Solution

I would use two cursors. The first runs through all databases - except sys dbs. For each database the logins will be fetched and added to the db_owner role.

If you have hundreds of dbs/ logins, I would write this as a db with some transaction handling and/or logging that if something crashes while executing you can continue on the point of crash.

i couldn't test the code below, this should be an approach how to develop your own proc

Use master
GO

DECLARE @dbname VARCHAR(50)          
DECLARE @SQL_statement varchar(max)
DECLARE @chng_role NVARCHAR(MAX)

DECLARE db_cursor CURSOR 
LOCAL FAST_FORWARD
FOR    
  SELECT name
    FROM MASTER.dbo.sysdatabases
    WHERE name NOT IN ('master','model','msdb','tempdb')    
OPEN db_cursor    
FETCH NEXT FROM db_cursor INTO @dbname    
WHILE @@FETCH_STATUS = 0    
 BEGIN 

    SET @SQL_statement = 'Declare  users_cursor CURSOR FOR SELECT name FROM '+ @dbname +'.sys.database_principals where (type='''S''' or type = '''U''')'
    EXEC sp_executesql @sqlstatement
    OPEN users_cursor
    FETCH NEXT FROM users_cursor INTO @UserName
    WHILE @@FETCH_STATUS = 0
        BEGIN       
            SET @chng_role = 'use [' + @dbname + ']; exec sp_addrolemember @rolename = ''db_owner'', @membername = ''' + @userName + ''''
            EXECUTE sp_executesql @chng_role 
            FETCH NEXT FROM users_cursor INTO @UserName
        END
    CLOSE users_cursor
    DEALLOCATE users_cursor

 FETCH NEXT FROM db_cursor INTO @dbname  
END  
CLOSE db_cursor  
DEALLOCATE db_cursor

Code Snippets

Use master
GO

DECLARE @dbname VARCHAR(50)          
DECLARE @SQL_statement varchar(max)
DECLARE @chng_role NVARCHAR(MAX)


DECLARE db_cursor CURSOR 
LOCAL FAST_FORWARD
FOR    
  SELECT name
    FROM MASTER.dbo.sysdatabases
    WHERE name NOT IN ('master','model','msdb','tempdb')    
OPEN db_cursor    
FETCH NEXT FROM db_cursor INTO @dbname    
WHILE @@FETCH_STATUS = 0    
 BEGIN 

    SET @SQL_statement = 'Declare  users_cursor CURSOR FOR SELECT name FROM '+ @dbname +'.sys.database_principals where (type='''S''' or type = '''U''')'
    EXEC sp_executesql @sqlstatement
    OPEN users_cursor
    FETCH NEXT FROM users_cursor INTO @UserName
    WHILE @@FETCH_STATUS = 0
        BEGIN       
            SET @chng_role = 'use [' + @dbname + ']; exec sp_addrolemember @rolename = ''db_owner'', @membername = ''' + @userName + ''''
            EXECUTE sp_executesql @chng_role 
            FETCH NEXT FROM users_cursor INTO @UserName
        END
    CLOSE users_cursor
    DEALLOCATE users_cursor

 FETCH NEXT FROM db_cursor INTO @dbname  
END  
CLOSE db_cursor  
DEALLOCATE db_cursor

Context

StackExchange Database Administrators Q#144096, answer score: 6

Revisions (0)

No revisions yet.