4

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.

6

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 
| improve this answer | |
  • 1
    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. – R Evans Jul 18 '16 at 14:06
  • @REvans you are right, I ll fix the code – Joe Platano Jul 18 '16 at 14:52
6

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.

| improve this answer | |
  • 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. – Joe Platano Jul 18 '16 at 21:23
  • @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. – Kenneth Fisher Jul 18 '16 at 21:53
  • 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. – QWE Jul 19 '16 at 16:33
  • 1
    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. – Kenneth Fisher Jul 19 '16 at 16:48
2

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.