Creating a user, mapping to a database and assigning roles to that database using SQL scripts

Question

I've done this before but not through scripts. I've to create a new user in SQL server (SQL Authentication) and map the user to a database and assign the roles for the user to that database using SQL scripts.

How can I do it?

Answer 1

Try:

CREATE USER [Username] FOR LOGIN [Domain\Username]
EXEC sp_addrolemember N'DatabaseRole', N'Username'

Obviously changing Username, and Domain\Username and the database role to being the username that you wish to grant rights to and the level of access, such as 'db_datareader'

Answer 2

The above solution works for me. However if the Username doesnt yet exist in that database as a USER, that username will not be fully mapped to the database role.

In this situation I first add the user:

USE databasename

CREATE USER [DOMAIN\svce_name] FOR LOGIN [DOMAIN\svce_name] WITH DEFAULT_SCHEMA=[dbo]
GO

Then I add the role:

USE databasename
EXEC sp_addrolemember N'db_ssisoperator', N'DOMAIN\svce_name'
GO