Sql-server – Is possible to have multiple database users with db_owner permission on a SQL database

sql server

I have setup a remote SQL server, which contains some databases. One user with certain login can create a database on server and after that can assign other users (each one with certain login) to new created database (as db_owner). Is possible to have multiple database users with db_owner permission on a SQL database? If yes how can i grant to other users the db_owner permission.

I've created a login with USE dbName;
CREATE USER [username] FOR LOGIN [username] ;
GO

EXEC sp_addrolemember 'db_owner', [username]

USE master
GRANT VIEW ANY DATABASE TO [username]; Now on my given database I have 2 users one is username and the other is dbo which is mapped on the user that created the database and is different form username. After that I'm using SMO to transfer this databases from remote server to localhost and while executing   transferDatabase.TransferData(); i'm getting the following error {"The login already has an account under a different user name."} . When I create the transfer instance i set also the property CopyAllUsers= true. How can I fix this.

Best Answer

Yes, definitely. The permission doesn't mean that the user is actually the owner of the database, but "only" that he/she has full control on it.

https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles

You can grant permissions using either SQL Server Management Studio or T-SQL:

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addrolemember-transact-sql
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-role-transact-sql