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 

• R Evans: That's a workable method. One suggestion though: Since sp_addrolemember operates within the current database. You'll need to make the call to that sproc dynamic as well, adding a USE statement before the stored procedure call.
• Joe Platano: @REvans you are right, I ll fix the code

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.

• Joe Platano: nice approach. is there also the possibility to add the db_owner role? i dont know why the OP asked to assign everyone to the role and i think as you it is not a good idea.
• Kenneth Fisher: @JoePlatano I tried to add db_owner to public but you can't change the membership of public. CONTROL database is almost identical to db_owner. Literally the only diff is the ability to add other people to db_owner. You can even grant others CONTROL.
• QWE: 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.
• Kenneth Fisher: Sometimes you have no choice, I understand completely. If you can (and aren't already) you might put the application on it's own isolated instance. I still stand by getting something in writing saying you are against this and not responsible for any damage/data loss/data theft caused by doing it.

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.