How To: Manage SQL Azure Security - Logins, Users, Roles, Schemas and Permissions

SQL Azure too has its security management system very similar to the SQL on-premise versions. It bascially consists of the following:
  1. Logins - Server level
  2. Users - Database level, mapped to server logins
  3. Schemas - Database level, authorized/owned by a user or another schema
  4. Roles - Database level, authorized/owned  by a user or another role
  5. Permissions - Database level, permission like SELECT, DELETE, ALTER etc. on objects/schemas granted to users/roles
Let's take a closer look  at each of these.

When you create a server in SQL Azure, it asks you to create a login at the same time.  That login acts as the administrative login, which has access to all databases in that server. However, you might want to create other logins with less privileges. As of now, the SQL Azure portal doesn't have any UI to create these extra logins. So you'll have to resort to running T-SQL statements.

Note: All of the below steps are performed using the administrative login mentioned above.

Creating Logins
Login to the master database and run the following t-sql statement.

CREATE LOGIN MyServerLogin WITH password='My#Password123'

This statement creates a login ID in the server. This a a normal login ID, which doesn't have access to any of the databases in that server. So if you try to login to the azure server with this account (either from SQL Server Management Studio 2008 R2 - SSMS  OR from Azure Portal) you would get an error saying this user doesn't have access to the master database. So the next step is to map this login to the required databases, not necessarily to the master db.

Mapping to Databases
Login to the required database where you want to grant access to the new login and run the following statement.

CREATE USER MyDatabaseUser FROM LOGIN MyServerLogin;

Now this login ID "MyServerLogin" has 'public' access the above database. Now you can successfully login to the azure serer using this login id and password and it will automatically connect to the above database.

Mapping to existing database roles
There are several pre-defined database roles. You have to add the user into any of the roles to give the user sufficient permissions to operate on the database.
 
EXEC sp_addrolemember 'db_datareader', 'MyDatabaseUser';

Creating custom roles
If none of the pre-defined roles suit your purpose, then you can create custom roles and grant hand-picked permissions to it.

-- Create the database role
CREATE ROLE MyDBRole AUTHORIZATION [dbo]
GO

Granting permissions to roles
Permissions to perform operations on a whole schema or individual objects can be granted to a whole role or a specific user.

Here in this example we are giving all possible permissions on the DBO schema (All database objects belong to this schema normally. However, custom schemas can be very well created)

-- Grant access rights to a specific schema in the database
GRANT
      ALTER,
      CONTROL,
      DELETE,
      EXECUTE,
      INSERT,
      REFERENCES,
      SELECT,
      TAKE OWNERSHIP,
      UPDATE,
      VIEW DEFINITION
ON SCHEMA::dbo
      TO MyDbRole
GO

-- Add an existing user to the new role created
EXEC sp_addrolemember 'MyDBRole', 'MyDBUser'
GO

Revoking permissions from roles
Permissions once given can be revoked from it as well using the DENY statement:

-- Revoke access rights on a schema from a role
DENY ALTER
ON SCHEMA::dbo
      TO MyDbRole

For more information on the T-SQL syntax, refer the below articles on MSDN:

CREATE LOGIN
CREATE USER
CREATE ROLE

Hope this helps to understand the fundamental security aspects of SQL Azure!

25 comments:

Bala Sakthis said...

Hi,
I was a bit confused with the Logins and Users. Lots of googling too didn't clarify.

Finally, the section "Mapping to Databases" in your blog gave the clarification. There are lots to learn from your blog. Please keep sharing.


Thanks!
Bala

Nava said...

Simple and Clear...Thanks for posting

Anonymous said...

Thank you for this simple and useful post.

shijisujai said...

Good article. I have shared some administration tips at my blog as well

http://thetechnologychronicle.blogspot.in/2013/11/securing-windows-azure-sql-using.html

http://thetechnologychronicle.blogspot.in/2013/11/azure-sql-administration-useful-commands.html

Hope its useful..

Lyudmil Petrov said...

Thank your so much for your simplicity and genuine approach. All the best

ahsan said...

thanks but when i logged in with new user created error occur
Login failed for user 'eb_User'.
This session has been assigned a tracing ID of 'xxxx-xxxx-xxxxx. Provide this tracing ID to customer support when you need assistance.

Hifni Shahzard Nazeer said...

Great Article!

Unknown said...

• Nice and good article. It is very useful for me to learn and understand easily. Thanks for sharing your valuable information and time. Please keep updatingAzure Online Training

Azure DevOps said...

It’s interesting to read content. nice post.
Microsoft Azure Online Training

Muralidhara Raju Indukuri said...

It is really nice website and informative.
aws training in hyderabad

anirudh said...

Thank you for sharing the article. The data that you provided in the blog is informative and effective.Best SQL Training Institute

Esha said...

Thanks for sharing this with us
Azure training
Azure certification
Azure Online training

keerthana said...

thanks for giving great kind of information. So useful and practical for me. Thanks for your excellent blog,
nice work keep it up thanks for sharing the knowledge.

PHP Training in Chennai | Certification | Online Training Course | Machine Learning Training in Chennai | Certification | Online Training Course | iOT Training in Chennai | Certification | Online Training Course | Blockchain Training in Chennai | Certification | Online Training Course | Open Stack Training in Chennai |
Certification | Online Training Course


vijay said...

After reading your article I was amazed. I know that you explain it very well. And I hope that other readers will also experience how I feel after reading your article.
Salesforce Training in Chennai

Salesforce Online Training in Chennai

Salesforce Training in Bangalore

Salesforce Training in Hyderabad

Salesforce training in ameerpet

Salesforce Training in Pune

Salesforce Online Training

Salesforce Training

Amrita Bansal said...

I like you post. It is very useful to all.
In this post having more useful information and improve our knowledge. Can you share more valuable information present Technology.
SQL Training in Gurgaon
Advanced Excel /VBA training in Gurgaon

MÜSLÜM SEVDA said...

adana escort - adıyaman escort - afyon escort - aksaray escort - antalya escort - aydın escort - balıkesir escort - batman escort - bitlis escort - burdur escort - bursa escort - diyarbakır escort - edirne escort - erzurum escort - eskişehir escort - eskişehir escort - eskişehir escort - eskişehir escort - gaziantep escort - gebze escort - giresun escort - hatay escort - ısparta escort - karabük escort - kastamonu escort - kayseri escort - kilis escort - kocaeli escort - konya escort - kütahya escort - malatya escort - manisa escort - maraş escort - mardin escort - mersin escort - muğla escort - niğde escort - ordu escort - osmaniye escort - sakarya escort - samsun escort - siirt escort - sincan escort - tekirdağ escort - tokat escort - uşak escort - van escort - yalova escort - yozgat escort - urfa escort - zonguldak escort

Unknown said...

youtube abone satın al /n trendyol indirim kodu
cami avizesi
cami avizeleri
avize cami
no deposit bonus forex 2021
takipçi satın al
takipçi satın al
takipçi satın al
takipcialdim.com/tiktok-takipci-satin-al/
instagram beğeni satın al
instagram beğeni satın al
btcturk
tiktok izlenme satın al
sms onay
youtube izlenme satın al
no deposit bonus forex 2021
tiktok jeton hilesi
tiktok beğeni satın al
binance
takipçi satın al
uc satın al
sms onay
sms onay
tiktok takipçi satın al
tiktok beğeni satın al
twitter takipçi satın al
trend topic satın al
youtube abone satın al
instagram beğeni satın al
tiktok beğeni satın al
twitter takipçi satın al
trend topic satın al
youtube abone satın al
takipcialdim.com/instagram-begeni-satin-al/
perde modelleri
instagram takipçi satın al
instagram takipçi satın al
takipçi satın al
instagram takipçi satın al
betboo
marsbahis
sultanbet

Unknown said...

instagram takipçi satın al
instagram takipçi satın al
takipçi satın al
takipçi satın al
instagram takipçi satın al
takipçi satın al
instagram takipçi satın al
aşk kitapları
tiktok takipçi satın al
instagram beğeni satın al
youtube abone satın al
twitter takipçi satın al
tiktok beğeni satın al
tiktok izlenme satın al
twitter takipçi satın al
tiktok takipçi satın al
youtube abone satın al
tiktok beğeni satın al
instagram beğeni satın al
trend topic satın al
trend topic satın al
youtube abone satın al
beğeni satın al
tiktok izlenme satın al
sms onay
youtube izlenme satın al
tiktok beğeni satın al
sms onay
sms onay
perde modelleri
instagram takipçi satın al
takipçi satın al
tiktok jeton hilesi
pubg uc satın al
sultanbet
marsbahis
betboo
betboo
betboo

merdoyeter said...

kayseriescortu.com - alacam.org - xescortun.com

Unknown said...

takipçi satın al
instagram takipçi satın al
https://www.takipcikenti.com

Unknown said...

marsbahis
betboo
sultanbet
marsbahis
betboo
sultanbet

Mohit Parashar said...

Digital Marketing Institute
IFDA is India's no 1 Computer Institute. Boost Your Career With IFDA .We provide various govt and non govt IT courses to all the desired students in India

Unknown said...

instagram beğeni satın al
yurtdışı kargo
seo fiyatları
saç ekimi
dedektör
fantazi iç giyim
sosyal medya yönetimi
farmasi üyelik
mobil ödeme bozdurma

Unknown said...

bitcoin nasıl alınır
tiktok jeton hilesi
youtube abone satın al
gate io güvenilir mi
referans kimliği nedir
tiktok takipçi satın al
bitcoin nasıl alınır
mobil ödeme bozdurma
mobil ödeme bozdurma

Unknown said...

SMM PANEL
smm panel
iş ilanları
İnstagram takipçi satın al
hirdavatciburada.com
beyazesyateknikservisi.com.tr
SERVİS
tiktok jeton hilesi

Post a Comment

Please let me know if you find it useful!