How to create users with limited access in SQL Azure?
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.