In this blog post, we will see how to mange the SQL Azure login and learn how to create users with limited access in SQL Azure.

Now, To create a user, we need to have a login. And we create a login by logging into Master database with server-level principal login or user with loginmanager role.

So now i log into Master database  of one of my SQL Azure server with server level principal login. And I run the following query to create a login named ‘parasdoshicom’:

create login parasdoshicom with password=’Passw0rd’

go

Quick note: choose a strong password – otherwise you may get an error. Strong passwords are those that has 8 character long with combination of symbols, numbers and letters. So i have ‘zero – 0’ in my password.

Now, we want to create a user in a user-database that has limited access. To do so, first login to that database. In my case, I want to create an user in the ‘adventureworksltaz2008r2’ and give him just the db_datareader role. let’s see how we do that.

So first I login to ‘adventureworksltaz2008r2’ with server-level principal login. And then:

1. run the command to create a user (let’s name it parasdoshicom which is same as the login name in this case) from the login parasdoshicom

create user parasdoshicom from login parasdoshicom

go

2. Assign the db_datareader role to user ‘parasdoshicom’

EXEC sp_addrolemember N’db_datareader’,N’parasdoshicom’

go

Now to test what we have just done. I am going to login to the “adventureworksltaz2008r2” database with parasdoshicom user credentials.

1. And I am going to run a select query <- This should work

2. And I am going to run a delete query <- This should not work

Let’s see:

1. select query with new login “parasdoshicom”

As expected, it runs!

2. Now let’s try running a delete command:

And it did not work. Notice that permission was denied. And that is what we intended, right?

So we were successful at creating a user “parasdoshicom” that can only read data from the “adventureworksltaz2008r2” database. And thus we have limited the access for the user in SQL Azure.

Related articles

• Quick summary of Ideas that I have logged at My Great Windows Azure Idea site (parasdoshi.com)
• Top two ways you can screw up the established SQL Azure connection from SSMS 2008 R2 running locally (parasdoshi.com)
• part 5 of “Getting started with SQL Azure” is live! (parasdoshi.com)