CODEWITHSUNDEEP
azureazure-sql-database
user763539
user763539

Reputation: 3689

Microsoft Azure : Add users and map them to a database + add database roles

I am brand new to Azure so please bear with me ...

Using export tool in SSMS I managed to copy all of my tables from local database to Azure. Now the most important part, adding users and mapping them to a database is unexplained.Also adding roles to database.Maybe it is explained but I sincerely dont understand a word of it. Seen some movies on YouTube but they mostly deal with database creation. SSMS is virtually useless for the Azure task (Or at least I do not know how). Also I can not find any tool on Azure dashboard to do it with.

So can someone to me explain in plain english the functionality of this stuff. What I could fathom is that you need to add users first to the Master database. Ok, I add user with a query:

 CREATE LOGIN  USER1  WITH PASSWORD = 'AG123SAL#'

So the user is added to the Master database.

Now, how do I map this user to a certain database? How do I set what he can do? And how do I add roles to my database and add user to role ? This is all very hard for a total newbie ...

Upvotes: 2

Views: 2548

Answers (2)

user3113204
user3113204

Reputation: 91

Look at this sql server authentication with Azure SQL Database tutorial

https://learn.microsoft.com/en-us/azure/sql-database/sql-database-control-access-sql-authentication-get-started

Upvotes: 0

TheGameiswar
TheGameiswar

Reputation: 28890

You can use Contained user database model,instead of old model..The way to do this is to connect to database on which you are trying to provide access to a user and run

CREATE USER mary WITH PASSWORD = 'strong_password';

to provide permissions to this user 

 ALTER ROLE dbmanager ADD MEMBER Mary;

Microsoft recommends this model ,when using SQLAzure since this is database as a service..Below is a quote from microsoft on same

As Microsoft evolves the SQL Database service and moves towards higher guaranteed SLAs you may be required to switch to the contained database user model and database-scoped firewall rules to attain the higher availability SLA and higher max login rates for a given database. Microsoft encourage you to consider such changes today.

If you are looking for old model of login-user based heirarchy,you can read below

to create login: 

create login mary with password='password'

Now to map user to database,you need to create user and map to that login.Login to the database ,you want to provide permissions and create user

create user marydb1 from login mary;

you also can assign roles as well

ALTER ROLE dbmanager ADD MEMBER Mary;

Updated as per comment :

create role 

create role rolename AUTHORIZATION db_manager*;

*:this should be user has total permissions on the database..since we may use this as base

now add permissions to that role 

grant select,update,delete to rolename;

now add users 

ALTER ROLE rolename ADD MEMBER username;

Upvotes: 2

Related Questions