Creating SQL Azure login for single database

Question

I want to create separate logins for each database we create on a SQL Azure database server which only have read / edit permissions for a single database and do not have drop permissions, and to automate this process as much as possible. One of the main reasons for doing this is to make sure that Entity framework drop and recreate scripts can't accidentally run against the database.

I found the following which looks very similar to what I'm trying to do

Allow user to do anything within his own schema but not create or drop the schema itself

Am I right in thinking I need to first create a login, then create a user, then create a role and add the user to this role?

CREATE LOGIN dblogin WITH password='m77hHmSk';
CREATE USER dbuser FROM LOGIN dblogin;
CREATE ROLE dbuserrole AUTHORIZATION dbo;
EXEC sp_addrolemember 'dbuserrole ', 'dbuser';
CREATE SCHEMA myschema AUTHORIZATION dbo;
GRANT ALTER, DELETE, EXECUTE, INSERT, REFERENCES, SELECT, UPDATE, VIEW DEFINITION ON SCHEMA::myschema TO dbuserrole ;

I've come up with the above.. am I close? Where would I specify the database which can be accessed?

Answer 1

Connect to master database and do the following:

CREATE LOGIN YourNewUser WITH PASSWORD = '<strong password here>';

Connect directly to database that the new user should own:

CREATE USER YourNewUser FOR LOGIN YourNewUser;
EXEC sp_addrolemember 'db_owner', 'YourNewUser';

• Jonathan Fite: Given the OP's security concerns, I would add that they could also create the user at the database level, and minimize further any "leakage". That does mean that if the database is restored anywhere that the user/password will go with it, however.

Answer 2

I've found it a lot easier to work with the Azure Active Directory, instead of SQL Logins. So I've created a group called "my-developers", and added the developers to the group, then I ran this on the database:

Create user [my-developers] from external provider 
alter ROLE db_owner ADD MEMBER [my-developers]

Then someone else can sort out the password problem. Your Grant statement looks as if it will prevent Entity Framework from damaging the database.