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
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?