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!

17 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.

Shiji 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!

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

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

merdoyeter said...

kayseriescortu.com - alacam.org - xescortun.com

Anonymous said...

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

Anonymous said...

en son çıkan perde modelleri
nft nasıl alınır
uc satın al
minecraft premium
en son çıkan perde modelleri
yurtdışı kargo
özel ambulans
lisans satın al

betmatik said...

Good text Write good content success. Thank you
kralbet
poker siteleri
slot siteleri
tipobet
kibris bahis siteleri
mobil ödeme bahis
betpark
bonus veren siteler

umut said...

https://saglamproxy.com
metin2 proxy
proxy satın al
knight online proxy
mobil proxy satın al
T2F1

Post a Comment

Please let me know if you find it useful!