How to Assign db_owner to All Users at Once in SQL Server

sql serverusers

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.

Best Answer

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

Related Solutions

Sql-server – Database performance for determining entities assigned to users

The short answer...

This has the hallmarks of solving the wrong problem with the wrong solution. It sounds like you're looking for a workaround to a fundamental design flaw, introducing an entirely new set of problems on the way.

An attempt at a longer answer...

It's almost impossible to offer any practical advice without a better understanding of the database, the application and interaction between the two. That said, there are one or two warning signs.

Logic in a table function

It sounds like you have a hierarchy of entities, which could be represented in a relational model and navigated in a set oriented fashion. If complicated logic is required in the database to navigate the hierarchy, the model is probably wrong.

Do you have a relational database or an object store/bit bucket?

Your question refers to entities, rather than tables and records. Did you build an object model and dump it to tables, rather than map your objects to a relational model?

How much data are you returning to the user?

A 2 second stored procedure call could be sub-optimal, accessing a sub-optimal model or returning too much data. Requiring 10 calls to this procedure suggests it could well be all 3. Navigating a 40k record graph is not big data territory, unless you're application is loading 40k records from the database to determine what's navigable.

You already have a better model

Your workaround may actually be the solution, if applied differently. If the output from your table function is a model which works, perhaps it should be a permanent part of the model. Instead of building this temporary table every time a user logs in, why not keep the data in that form?

Sql-server – Handling growing number of Tenants in Multi-tenant Database Architecture

At the lower end (500 tenants / 10000 users) this is how I did it. First, you have a "control" database that is global, central and contains all of the information about tenants and users (I really don't think you want to manage these as SQL auth logins). So imagine a database called "Control" with the following tables:

CREATE TABLE dbo.Instances
(
  InstanceID INT PRIMARY KEY,
  Connection VARCHAR(255)
  --, ...
);

INSERT dbo.Instances SELECT 1, 'PROD1\Instance1';
INSERT dbo.Instances SELECT 1, 'PROD2\Instance1';
-- ...

CREATE TABLE dbo.Tenants
(
  TenantID INT PRIMARY KEY,
  Name NVARCHAR(255) NOT NULL UNIQUE,
  InstanceID INT -- Foreign key tells which instance this tenant's DB is on
  --, ...
);

INSERT dbo.Tenants SELECT 1, 'MyTenant', 1;
-- ...

CREATE TABLE dbo.Users
(
  UserID INT PRIMARY KEY,
  Username VARCHAR(320) NOT NULL UNIQUE,
  PasswordHash VARBINARY(64), -- because you never store plain text, right?
  TenantID INT -- foreign key
  --, ...
);

INSERT dbo.Users SELECT 1, 'foo@bar.com', 0x43..., 1;

In our case when we added a new tenant we would build the database dynamically, but not when the admin user clicked OK in the UI... we had a background job that pulled new databases off a queue every 5 minutes, set model to single_user, and then created each new database serially. We did this to (a) prevent the admin user from waiting for database creation and (b) to avoid two admin users trying to create a database at the same time or otherwise getting denied the ability to lock model (required when creating a new database).

Databases were created with the name scheme Tenant000000xx where xx represented Tenants.TenantID. This made maintenance jobs quite easy, instead of having all kinds of databases named BurgerKing, McDonalds, KFC etc. Not that we were in fast food, just using that as an example.

The reason we didn't pre-allocate thousands of databases as the comment suggested is that our admin users usually had some idea of how big the tenant would become, whether they were high priority, etc. So they had basic choices in the UI that would dictate their initial size and autogrowth settings, which disk subsystem their data/log files would go to, their recovery settings, backup schedule to hinge off of, and even smarts about which instance to deploy the database to in order to best balance usage (though our admins could override this). Once the database is created, the tenant table was updated with the chosen instance, an admin user was created for the tenant, and our admins were e-mailed the credentials to pass along to the new tenant.

If you're using a single point of entry, it is not feasible to allow multiple tenants to have users with the same username. We opted to use e-mail address, which - if all users work for the company and use their corporate e-mail address - should be fine. Though our solution eventually became more complex for two reasons:

We had consultants that worked for more than one of our clients, and needed access to multiple
We had tenants who themselves were actually comprised of multiple tenants

So, we ended up with a TenantUsers table that allowed one user to be associated with multiple tenants.

Initially when a user logs in, the app will know the connection string for the control database only. When a login is successful, it can then build a connection string based on the information it found. E.g.

SELECT i.Connection
  FROM dbo.Instances AS i
  INNER JOIN dbo.Tenants AS t
  ON i.InstanceID = t.InstanceID
  INNER JOIN dbo.TenantUsers AS u
  ON i.TenantID = u.TenantID
  WHERE u.UserID = @UserID;

Now the app could connect to the user's database (each user had a default tenant) or the user could select from any of the tenants they could access. The app would then simply retrieve the new connection string, and redirect to the home page for that tenant.

If you get into this 10MM user area you propose, you'll definitely need this to be balanced better. You may want to federate the application so that they have different points of entry connecting to different control databases. If you give each tenant a subdomain (e.g. TenantName.YourApplicationDomain.com) then you can do this behind the scenes with DNS/routing without interrupting them when you need to scale out further.

There is a lot more to this - like @Darin I am only scratching the surface here. Let me know if you need a non-free consult. :-)

Best Answer

Related Solutions

Sql-server – Database performance for determining entities assigned to users

Sql-server – Handling growing number of Tenants in Multi-tenant Database Architecture

Related Question