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

I followed the answers here but when I tried to connect with my new user, I got an error message stating "The server principal 'newuser' is not able to access the database 'master' under the current security context".

I had to also create a new user in the master table to successfully log in with SSMS.

USE [master]
GO
CREATE LOGIN [newuser] WITH PASSWORD=N'blahpw'
GO
CREATE USER [newuser] FOR LOGIN [newuser] WITH DEFAULT_SCHEMA=[dbo]
GO

USE [MyDatabase]
CREATE USER newuser FOR LOGIN newuser WITH DEFAULT_SCHEMA = dbo
GO
EXEC sp_addrolemember N'db_owner', N'newuser'
GO

Answer 2

I found this link very helpful:
https://azure.microsoft.com/en-gb/documentation/articles/sql-database-manage-logins/

It details things like:
- Azure SQL Database subscriber account
- Using Azure Active Directory users to access the database
- Server-level principal accounts (unrestricted access)
- Adding users to the dbmanager database role

I used this and Stuart's answer to do the following:
On the master database (see link as to who has permissions on this):

CREATE LOGIN [MyAdmin] with password='ReallySecurePassword'

And then on the database in question:

CREATE USER [MyAdmin] FROM LOGIN [MyAdmin]
ALTER ROLE db_owner ADD MEMBER [MyAdmin]

You can also create users like this, according to the link:

CREATE USER [[e-mail]] FROM EXTERNAL PROVIDER;

Answer 3

I use the Azure Management console tool of CodePlex, with a very useful GUI, try it. You can save type some code.

Answer 4

create a user and then add user to a specific role:

CREATE USER [test] WITH PASSWORD=N'<strong password>'
go
ALTER ROLE [db_datareader] ADD MEMBER [test]
go

Answer 5

Edit - Contained User (v12 and later)

As of Sql Azure 12, databases will be created as Contained Databases which will allow users to be created directly in your database, without the need for a server login via master.

CREATE USER [MyUser] WITH PASSWORD = 'Secret';
ALTER ROLE [db_datareader] ADD MEMBER [MyUser];

Note when connecting to the database when using a contained user that you must always specify the database in the connection string.

Traditional Server Login - Database User (Pre v 12)

Just to add to @Igorek's answer, you can do the following in Sql Server Management Studio:

Create the new Login on the server
In master (via the Available databases drop down in SSMS - this is because USE master doesn't work in Azure):

create the login:

CREATE LOGIN username WITH password='password';

Create the new User in the database
Switch to the actual database (again via the available databases drop down, or a new connection)

CREATE USER username FROM LOGIN username;

(I've assumed that you want the user and logins to tie up as username, but change if this isn't the case.)

Now add the user to the relevant security roles

EXEC sp_addrolemember N'db_owner', N'username'
GO

(Obviously an app user should have less privileges than dbo.)

Answer 6

I think the templates use the following notation: variable name, variable type, default value.

Sysname is a built-in data type which can hold the names of system objects.

It is limited to 128 Unicode character.

-- same as sysname type
declare @my_sysname nvarchar(128);