When you generate a SQL Azure server via the SQL Azure portal, you generate a user name and password at the same time. This is your administrative account it has access to all databases on that server. However, you might want to give other people access to some or all of your databases on that server, with full or restricted permissions. This article will show you how to generate additional user accounts on your SQL Azure databases.
Currently, the SQL Azure portal does not allow you to administrate additional users and logins, in order to do this you need to use Transact-SQL. The easiest way to execute Transact -SQL against SQL Azure is to use the SQL Server Management Studio 2008 R2. Learn more about attaching it to SQL Azure here. SQL Server Management Studio 2008 R2 will list the users and logins associated with the databases; however, at this time it does not provide a graphical user interface for creating the users and logins.
Logins are server wide login and password pairs, where the login has the same password across all databases. Here is some sample Transact-SQL that creates a login:
CREATE LOGIN readonlylogin WITH password='1231!#ASDF!a';
You must be connected to the master database on SQL Azure with the administrative login (which you get from the SQL Azure portal) to execute the CREATE LOGIN command. Some of the common SQL Server logins can be used like sa, Admin, root, for a complete list click here.
Users are created per database and are associated with logins. You must be connected to the database in where you want to create the user. In most cases, this is not the master database. Here is some sample Transact-SQL that creates a user:
CREATE USER readonlyuser FROM LOGIN readonlylogin;
Just creating the user does not give them permissions to the database. You have to grant them access. In the Transact-SQL example below the readonlyuser is given read only permissions to the database via the db_datareader role.
EXEC sp_addrolemember 'db_datareader', 'readonlyuser';
Fortunately, SQL Server Management Studio 2008 R2 does allow you to delete users and logins. To do this traverse the Object Explorer tree and find the Security node, right click on the user or login and choose Delete.
One thing to note is that SQL Azure does not allow the USE Transact-SQL statement, which means that you cannot create a single script to execute both the CREATE LOGIN and CREATE USER statements, since those statements need to be executed on different databases.
There is additional information about Managing Databases and Logins in SQL Azure on MSDN.
Do you have questions, concerns, comments? Post them below and we will try to address them.
Could you please tell me if I want to sync on-premise db with sql azure db, what type of user and permission are requried for azure db user ?
Is there any sample script for the same?
I have followed above article and create user for db on azure using mgmt. studio 2008 r2,
and added db_owner permission.
But sync requries create procedure permission which I am not able to grant using sql server administrator.
Bipin: In order to execute CREATE PROCEDURE the user is going to need db_ddladmin permissions.
Hi, I need to create a stored procedure in the master database in SQL Azure. I tried to grant the administrative user (the user that was created for my SQL Azure server) db_ddladmin permissions but got the error User does not have permission to perform this action. Is there something else I need to do before my administrative user has the right permission to create procedure? Thanks for your help.
The master database is owned by SQL Azure, not the server administrator. Why do you need to create a stored procedure in the master database?
I created a worker role to perform daily database backup using the CREATE DATABASE targetdatabasename AS COPY OF sourcedatabasename command. I can't run this command in dynamic sql so I created a stored procedure that is used by the worker role. I also need to run the command in the master database. This is why I need to be able to create a stored procedure in the master database.
Robinson: CREATE DATABASE is a special command, since it gets executed on the front end (not on the databases), telling SQL Azure Platform to create a database and replicas. It can not be put in a stored procedure in master, in fact no stored procedures can be added to master. Why can't you run the command in dynamic SQL?
I guess I must have misunderstood your statement in your blog blogs.msdn.com/.../10054109.aspx.
Dynamic sql statement does not allow ‘Create Database’ statements in SQL Azure, the usual error is:
Msg 40530, Level 15, State 2, Line 1
The CREATE DATABASE statement must be the only statement in the batch.
I will update my worker role to use dynamic sql to see if it will work. Thanks.
Robinson: Sorry about that, I ment dynamic T-SQL generated from within T-SQL, not from a calling client.
No problem. I should have tried it out anyway :) Thanks for the great blogs. I have learned a lot from your blogs.
Perhaps you can help me with this problem since you've been of great assistance before. I am having a really difficult time creating users for the SQL Azure database. I can't login with the login/password after it's created. After four attempts through the process I decided it was time to ask for help....
The steps I'm following:
1. Connecting to the master database and creating a login
create login balogin WITH PASSWORD = 'xxxxxxx';
2. Connected to the application database, 'tqm' and creating the user
CREATE USER businessadvisor FROM LOGIN balogin;
3. Giving the user read and write access
EXEC sp_addrolemember 'db_datawriter', 'businessadvisor'
EXEC sp_addrolemember 'db_datareader', 'businessadvisor'
I believe the above instructions mimic the instructions on this blog and other sites. Even so, when I try to login to the 'tqm' with the new user businessadvisor I get an error message. I've tried this numerous time even created the user on the master database plus gave them 'db_owner' rights all to no avail.
I'm sure I'm missing something really simple so please take a look and let me know. Thanks in advance for any help you can give.
Drew: Did you try login with: balogin?
I just tried and balogin DOES work.
I really appreciate you taking a look since I know your job isn't tech support.
If you are still monitoring this post, could you provide some assistance?
I am having the exact same issue as Drew. Created a new login on the master db and then created user from login on the DB I want the user to access. When using this newly created user i cannot access the db. I can however access with the login created. but i do not want to give this out to the user because they can also access the entire sql azure account with this login. Could I set permissions on the Login to only allow access through my application to the SQL Azure, but not be able to manage the account?
How to change the permissions of the user for the DB like from readonly to restricted access etc?
I think that this link
would be more useful to connect SQL Server Management studio to SQL Azure instance.