43

I would like to create an SQL Azure user and grant her read-only access on a handful of DBs, what script can I use to achieve this?

Luiso
  • 4,173
  • 2
  • 37
  • 60
Sam Saffron
  • 128,308
  • 78
  • 326
  • 506

3 Answers3

98

A pure TSQL script is super messy, SQL Azure disables the USE command, so you are stuck opening connections to each DB you need to give the user read access.

This is the gist of the pattern.

In Master DB:

CREATE LOGIN reader WITH password='YourPWD';
-- grant public master access
CREATE USER readerUser FROM LOGIN reader;

In each target DB (requires a separate connection)

CREATE USER readerUser FROM LOGIN reader;
EXEC sp_addrolemember 'db_datareader', 'readerUser';
Sam Saffron
  • 128,308
  • 78
  • 326
  • 506
  • 6
    Why is there a "CREATE USER..." in master DB? In all docs I looked at, it only says you have to create user in target DB...Just curious... – zam6ak Oct 27 '14 at 20:21
  • 15
    Ancient answer and old comment, but for anyone interested... I think he's included the create user in master DB so the user (readerUser) can login to the server instance via Management Studio - if the user doesn't exist under master, they won't be able to use SQL authentication in Management Studio to sign in. – shadowf Apr 11 '16 at 17:52
  • 2
    Grant permissions at object level or **for selected tables** check [this](http://stackoverflow.com/questions/3998634/sql-server-2008-how-do-i-grant-privileges-to-a-username) and [this](http://stackoverflow.com/questions/11086967/how-to-permit-a-sql-server-user-to-insert-update-delete-data-but-not-alter-sche) , hope helps. – Shaiju T Sep 27 '16 at 09:43
4

OR... Use the Azure User Management console - AUMC to manage the Logins and Users.

It's a open source project available on codeplex AUMC.codeplex.com

Project Description

Azure User Management Console - AUMC is a User Graphic Interface (GUI) that manages the users and logins of an Azure SQL database. The tool is simply converting your action into T-SQL commands and execute them on the Azure SQL Database.

A quick simple tool with a user interface! Don

Enjoy!

Frank Boucher
  • 1,834
  • 20
  • 25
  • 22
    It's a little spooky to plug your database credentials into apps just downloaded from the internet ... just saying ... – DeepSpace101 Aug 18 '16 at 04:23
-2

You can create new user without creating login on master DB (which is require make a separate connection)

CREATE USER user1 WITH password='<Strong_Password>';

https://azure.microsoft.com/en-us/documentation/articles/sql-database-manage-logins/

Yury Dzhantuganov
  • 57
  • 4