How do I create a new user in a SQL Azure database

Question

I am trying to use the following template:

-- =================================================
-- Create User as DBO template for SQL Azure Database
-- =================================================
-- For login <login_name, sysname, login_name>, create a user in the database
CREATE USER <user_name, sysname, user_name>
    FOR LOGIN <login_name, sysname, login_name>
    WITH DEFAULT_SCHEMA = <default_schema, sysname, dbo>
GO

-- Add user to the database owner role
EXEC sp_addrolemember N'db_owner', N'<user_name, sysname, user_name>'
GO

I would like to create a user called user1 with a password of 'user1pass'. I connected with my default database 'authentication' and I have a query window open.

But the template does not make sense for me. For example what's sysname, where do I supply the password and what should I use as the default_schema?

The particular user needs to have the power to do everything. But how do I set it up so he can do everything, is that done if I make the user a database owner?

So far I have tried:

CREATE USER user1, sysname, user1
    FOR LOGIN user1, sysname, user1
    WITH DEFAULT_SCHEMA = dbo, sysname, dbo
GO

Giving:

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near ','.

and:

CREATE USER user1
    FOR LOGIN user1
    WITH DEFAULT_SCHEMA = dbo
GO

Giving:

Msg 15007, Level 16, State 1, Line 1 'user1' i

s not a valid login or you do not have permission.

Answer 1

Check out this link for all of the information : https://azure.microsoft.com/en-us/blog/adding-users-to-your-sql-azure-database/

First you need to create a login for SQL Azure, it's syntax is as follows:

CREATE LOGIN username WITH password='password';

This command needs to run in master db. Only afterwards can you run commands to create a user in the database. The way SQL Azure or SQL Server works is that there is a login created first at the server level and then it is mapped to a user in every database.

HTH

Answer 2

You can simply create a contained user in SQL DB V12.

Create user containeduser with password = 'Password'

Contained user login is more efficient than login to the database using the login created by master. You can find more details @ http://www.sqlindepth.com/contained-users-in-sql-azure-db-v12/