Sql-server – Creating SQL Azure login for single database

azure-sql-databasesql server

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?

Best Answer

Connect to master database and do the following:

CREATE LOGIN YourNewUser WITH PASSWORD = '&lt;strong password here&gt;';

Connect directly to database that the new user should own:

CREATE USER YourNewUser FOR LOGIN YourNewUser;

EXEC sp_addrolemember 'db_owner', 'YourNewUser';

Related Solutions

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

There is no need to grant CONTROL on the schema.
The permission required to DROP SCHEMA is either CONTROL on the schema or ALTER ANY SCHEMA at the database level, and that is why your user was able to drop the schema. Removing these two permissions will prevent the role-associated users from creating and droping the schema (unless they have higher level permissions of course).

The required permission to CREATE ALTER and DROP other objects is the CREATE permission for the object type (table\procedure\function\view) combined with ALTER permission on the schema.
You already have these permissions in your script, so all that you have to do is remove the CONTROL permission. For reference, here is a BOL list of DDL statements where You can find the required permission for all object types.

For the lazy, here is your code after removing the unnecessary permission:

CREATE ROLE myrole AUTHORIZATION dbo;
EXEC sp_addrolemember 'myrole', 'myuser';

CREATE SCHEMA myschema AUTHORIZATION dbo;

GRANT ALTER, DELETE, EXECUTE, INSERT, REFERENCES, SELECT,
          UPDATE, VIEW DEFINITION ON SCHEMA::myschema TO myrole;

GRANT CREATE TABLE, CREATE PROCEDURE, CREATE FUNCTION, CREATE VIEW TO myrole;

SQL Server 2014 – How to Assign dbo Role to Login Without Creating New User

If at all possible, this should be fixed from the deployment side of things and not from a questionable modification of the target database side of things (referring to the conversation in the comments on the Question related to using the deprecated sp_addalias).

Depending on what the exact problem is, there are various configuration options of DacPac deployment that can be used to get around such conflicts. These options can either be specified on the command line if running SqlPackage, or can be placed into a DAC Publish Profile (i.e. an XML config file).

The options of interest here are listed in the Publish Parameters, Properties, and SQLCMD Variables section of the SqlPackage page.

To start with, do you have any of the following set to True:

DropObjectsNotInSource (default: False)
DropRoleMembersNotInSource (default: False)
IgnoreLoginSids (default: True)

If you have the DropObjectsNotInSource property set to True, then try adding:

DoNotDropObjectType=Users
ExcludeObjectType=Users
IgnoreRoleMembership=True
IgnoreUserSettingsObjects=True ??

If needing to specify the ExcludeObjectType property, that would look as follows, depending on where it is specified:

On the command-line: /p:ExcludeObjectType=Users

In a DAC Publish Profile:

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="12.0"
         xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
    <PropertyGroup>
        <ExcludeObjectType>Users</ExcludeObjectType>
    </PropertyGroup>
</Project>

Best Answer

Related Solutions

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

SQL Server 2014 – How to Assign dbo Role to Login Without Creating New User

Related Question