Assign db_owner to all users at once

Question

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.

Answer 1

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 

Answer 2

I'm going to start by saying this is a bad, bad, bad idea. Does everyone on the instance need the ability to drop any database they want? (probably without a backup) Or truncate the tables? Or encrypt/decrypt columns? etc. DB_OWNER is an administrative permission and needs to be strictly controlled. I'm honestly against anyone having DB_OWNER except DBAs that you don't plan on giving SYSADMIN. That being said I like to know how to do things so here is a slightly easier way to handle it.

First I'm using the CONTROL database permission. This is roughly equivalent to DB_OWNER with the main difference being you can't add members to the DB_OWNER role. Next I'm granting it to the PUBLIC role. Everyone has PUBLIC so everyone would get the permission in one fell swoop. This way you only need the one loop. FYI I'm using sp_msforeachdb for this demo, even though you shouldn't use it. For actual code use Aaron Bertrand's replacement code.

EXEC sp_msforeachdb 'USE [?]; GRANT CONTROL ON database::[?] TO public'

This will cover all new IDs and new databases (the permissions are being granted in Model as well). The nicest thing about this is the fact that you can reverse it equally easily.

If you are being forced to do this for political reasons then I would request something in writing that absolves you of the inevitable problems that WILL occur.

I talked about not being a sysadmin here. I realize it's sysadmin not db_owner but the same arguments apply.

And administrative principals, roles and permissions here.

Answer 3

If you can use PowerShell, here is an even simpler solution (two lines)

#please replace: 
#<machine_name> to your proper server name
#<instance_name> to your sql instance name, if your sql instance is a default instance, using DEFAULT for <instance_name>

Import-Module sqlps -DisableNameChecking; 
dir SQLSERVER:\SQL\<machine_name>\<instance_name>\databases | % {$_.users } | ? {(-not $_.IsSystemObject)} | % {$_.addtorole('db_owner')}

The beauty of using PowerShell is that you can run this line from you laptop against all sql servers (to which you have the proper privileges).

Note:I agree with Kenneth Fisher's comment that it is not a good idea to grant everyone db_owner privilege unless it is really really justified.