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  lt login name  sysname  login name gt   create a user in the database CREATE USER  lt user name  sysname  user name gt      FOR LOGIN  lt login name  sysname  login name gt      WITH DEFAULT SCHEMA    lt default schema  sysname  dbo gt  GO  -- Add user to the database owner role EXEC sp addrolemember N db owner   N  lt user name  sysname  user name gt   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

User · Accepted Answer

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, its 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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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  mike contoso com  FROM EXTERNAL PROVIDER

User · Answer

create a user and then add user to a specific role   CREATE USER  test  WITH PASSWORD N  lt strong password gt   go ALTER ROLE  db datareader  ADD MEMBER  test  go

User · Answer

1 Create login while connecting to the master db  in your databaseclient open a connection to the master db  CREATE LOGIN  testUserLogin  WITH password  1231  ASDF a   2 Create a user while connecting to your db  in your db client open a connection to your database  CREATE USER testUserLoginFROM LOGIN testUserLogin  Please  note  user name is the same as login  It did not work for me when I had a different username and login  3 Add required permissions EXEC sp addrolemember db datawriter   testUser   You may want to add  db datareader  as well  list of the roles  https   docs microsoft com en-us sql relational-databases security authentication-access database-level-roles view sql-server-ver15 I was inspired by  nthpixel answer  but it did not work for my db client DBeaver  It did not allow me to run USE  master  and use  my-db  statements  https   azure microsoft com en-us blog adding-users-to-your-sql-azure-database  How to test your user  Run the query bellow in the master database connection  SELECT A name as userName  B name as login  B Type desc  default database name  B   FROM sys sysusers A     FULL OUTER JOIN sys sql logins B        ON A sid   B sid WHERE islogin   1 and A sid is not null  List of all users in Azure SQL

[sql-server] How do I create a new user in a SQL Azure database?

Examples related to sql-server

Examples related to azure

Examples related to azure-sql-database