Creating new user/login in sql azure

27
votes

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.

5
sqlazure-sql-database

5 Answers

29
votes

You can also user 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!

Enjoy!

29
votes

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
4
votes

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

2
votes

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