How to add the user to Windows Azure Sql Server?

Damir Dobric Posts

Next talks:

 

    

Follow me on Twitter: #ddobric



 

 

Archives

 


When working with SQL Server in Windows Azure you can create very quickly the database. But if you have a big distributed team usually you will not want to give everybody the access to DB.
Unfortunately right now, there is no way to manage users in Management Portal or event to do that within SQL Management studio. At least not by using common UI. This post describes stepp by step how to create the user
by using T-SQL within SQL Management Studio.

Start SQM Management studio and connect with the admin user, which you will usually use in SQL Management Portal.
Once you are connected to your Windows Azure Database, select the master database, right mouse click and select new query.

image

This will open the new query wit connection to master database. Note that you have to open the SQL editor with master database!
Then type following in the query in the editor:

CREATE LOGIN yourusername WITH password=’YourPwd123‘;

This will create the new LOGIN in the master database.

Now execute following statement in the same editor:

CREATE USER yourusername FROM LOGIN yourusername ;

This statement will create the user from specified login in the master database.


After that we need the to select wanted database (as in previous step, but this time you will not select master database) and open the query window.
In that window enter same script as in previous step:

CREATE USER yourusername FROM LOGIN yourusername ;

This statement will create the user from specified login in your database.

At the end we need to append some roles to the user. They can be enlisted as follows: 
image

For example if you want to add it in the role of owners do following:

EXEC sp_addrolemember 'db_owner', yourusername ;

Now you can use use DB in the cloud. I know that last few steps are not very common task of every developer. I have tried to keep them simple. T-SQL can look much worse, believe me.
An alternative to these steps was to setup completely new infrastructure which will publicly provide secure access to database. My steps have taken about 20 mins to provision and migrate one DB in the cloud.
To do all that On-Prem will take much, much more time.


Posted Apr 16 2013, 09:09 PM by Damir Dobric
Filed under:
developers.de is a .Net Community Blog powered by daenet GmbH.