SQL Server giving logins(users) db_owner access to database

Solution 1:

You need to do two things, both running in the context of the target database (i.e., execute USE (database) first):

1. Add that user as a login to the database: CREATE USER [LoginName] FOR LOGIN [LoginName]
2. Add that user to the role: EXEC sp_addrolemember N'db_owner', N'LoginName'

In general, if you have SQL Server Management Studio 2005 or higher, you can go into the UI for an operation, fill out the dialog box (in this case, assigning the user to the database & adding roles), and then click the "Script" button at the top. Instead of executing the command, it will write a script for the action to a new query window.

Solution 2:

I'd like to propose another solution which may help someone...

-- create the user on the master database
USE [master] 
GO
CREATE LOGIN [MyUserName] WITH PASSWORD=N'MyPassword'
CREATE USER [MyUserName] FOR LOGIN [MyUserName]
GO

-- create the user on the target database for the login
USE [MyDatabaseName]
GO
CREATE USER [MyUserName] FOR LOGIN [MyUserName]
GO

-- add the user to the desired role
USE [MyDatabaseName]
GO
ALTER ROLE [db_owner] ADD MEMBER [MyUserName]
GO

Solution 3:

Use sp_addrolemember

EXEC sp_addrolemember 'db_owner', 'MyUser'

Solution 4:

Here is how to use ALTER ROLE with an existing server login named MyLogin.

USE MyDatabase
CREATE USER MyLogin FOR LOGIN MyLogin
ALTER ROLE db_owner ADD MEMBER MyLogin
GO

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-role-transact-sql