Assign db_owner to all users at once
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
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.
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.