Creating new user/login in sql azure

Question

Create a new user/login in sql azure with access to read/insert/update on the database items like tables sp,view etc.

This user will not have the permission to drop table/drop procedures.

Please give me an example.

Answer 1

First connect to the server and switch to the master database. In master create a login and then add a user for that login to the master database.

CREATE LOGIN [MyLogin] WITH password='xxxxxxxxx'
GO

CREATE USER [MyUser] FOR LOGIN [MyLogin] WITH DEFAULT_SCHEMA=[dbo] 
GO

Next connect/switch to the database you want the new user for. Create a user in that database

CREATE USER [MyUser] FOR LOGIN [MyLogin] WITH DEFAULT_SCHEMA=[dbo]
GO


EXEC sp_addrolemember 'db_datareader', 'MyUser';
GO

EXEC sp_addrolemember 'db_datawriter', 'MyUser';
GO

GRANT EXECUTE ON SCHEMA :: dbo TO MyUser;
GO

Answer 2

You can also use the Azure User Management console - AUMC to manage the Logins and Users.

It's an open-source project available on CodePlex AUMC.codeplex.com

---

UPDATE: Since CodePlex has been retired (thanks to @Danny the code was saved), I recreated a repo in GitHub and created a release.

This new version uses .NET 4.8

---

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 executing them on the Azure SQL Database.

A quick simple tool with a user interface!

Enjoy!

Answer 3

please read this article from Microsoft on how to properly create logins, users and assigning access rights in SQL Azure: Managing Databases and Logins

Then, in order to assign or deny specific permissions, review this article from Microsoft as well: Granting Access to a Database Object

And here is the link to specifically deny access to permissions: Deny Object Permissions

Note that you can also apply permissions to schemas. A schema is a container of database objects on which you can assign permissions. So you could easily place all your stored procedures in a single schema that you created to that effect, deny alter/drop permission, and grant execute on the schema directly. This way, all the objects within that schema will inherit the permissions defined. Here is the article for schema permissions: GRANT Schema Permission

Answer 4

Also you can do it manually by assigning proper user roles. Check out article: How to create custom user login for Azure SQL Database

Answer 5

Some Azure sql administration tips can be found here

http://thetechnologychronicle.blogspot.in/2013/11/azure-sql-administration-useful-commands.html

http://thetechnologychronicle.blogspot.in/2013/11/securing-windows-azure-sql-using.html