SQL Server giving logins(users) db_owner access to database

We have a test database and some test logins that we would like to give db_owner access to through a script. Usually we would have to go into logins and right c开发者_运维知识库lick on the username and go to user mapping and select the database to associate it with and give it owner access and click OK.

---

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.

---

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

---

Use sp_addrolemember

EXEC sp_addrolemember 'db_owner', 'MyUser'

---

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://learn.microsoft.com/en-us/sql/t-sql/statements/alter-role-transact-sql

---

Add login, add user with default schema 'dbo', add user to role, and also be able to run it again with no errors, and on Azure or local SQL Servers:

--note login and user have same name
-- change the names of: DBNAME, PASSWORDHERE and LOGINNAMEHERE

--1 create login
use master
GO
IF not EXISTS (SELECT * FROM sys.sql_logins WHERE name = N'LOGINNAMEHERE')
BEGIN
    CREATE LOGIN [LOGINNAMEHERE] WITH PASSWORD=N'PASSWORDHERE'
END

--2 on DB create User
use DBNAME
GO

if (Not Exists(select * from sys.sysusers where name = 'LOGINNAMEHERE'))
BEGIN;
    CREATE USER [LOGINNAMEHERE] FOR LOGIN [LOGINNAMEHERE] WITH DEFAULT_SCHEMA=[dbo]
END;
GO

--3 insert role membership
IF ((select ISNULL(is_rolemember('db_owner', 'LOGINNAMEHERE'),0)) <> 1)
BEGIN;
    --EXEC sp_addrolemember 'db_owner', 'LOGINNAMEHERE'
    ALTER ROLE [db_owner] ADD MEMBER [LOGINNAMEHERE];
END;
IF ((select ISNULL(is_rolemember('db_owner', 'LOGINNAMEHERE'),0)) <> 1)
BEGIN;
    PRINT 'user not added to role try sp_addrolemember';
END;