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.