Sql-server – Need to deny DML access through SSMS using web app credentials

to do it correctly is not very simple, although once setup, your done.

Data users or application roles default have database scoped permissions. Database users are linked to Logins and therefore can have server wide permissions from their Login. Application roles aren't linked and can only get server wide permission by using a form of impersonation.

Any way to give these server scope permissions by means of impersonating will fail by default.This is by design.

You have 2 options:

1. Tell SQL Server that you as a DBA trust any user in that database to have privilegde escalation to server wide permission if impersonation is used. You acomplish this by setting the option TRUSTWORTHY to ON However if you don't fully understand what this option does first read: SQL Server 2012 Best Practice Whitepaper (Specifically page 18 and 19 about "Database Ownership and Trust")
2. Use signed stored procedures to accomplish your task.

I'll first give you the example of option 1:

CREATE LOGIN [appRoleProxy] WITH PASSWORD ='1234abcd!@'
GO

EXEC sp_addsrvrolemember    @loginame='appRoleProxy',
                            @rolename='securityadmin'

GO

CREATE DATABASE [Stefaan]
go

ALTER DATABASE [Stefaan] SET TRUSTWORTHY ON
GO

USE [Stefaan]
GO

--add the proxy account to the dbo role since your stored procedure will be running in the context of this account and is also accessing your user database.
ALTER ROLE [db_owner] ADD MEMBER [appRoleProxy]
GO

CREATE APPLICATION ROLE appRole WITH PASSWORD ='Passw0rd!';
GO

CREATE PROCEDURE dbo.usp_testproc 
@LoginName nvarchar(128),
@Password nvarchar(128),
@DBName nvarchar(128)
WITH EXECUTE AS 'appRoleProxy' --Impersonate a SQL server login with the proper server level permissions
AS
DECLARE @SQL varchar(max)
BEGIN
    IF NOT EXISTS ( SELECT  name
                    FROM    sys.server_principals
                    WHERE   name = @LoginName )
        BEGIN
            SET @SQL = 'CREATE LOGIN [' + @LoginName + '] WITH PASSWORD = '''
                + @Password + ''', DEFAULT_DATABASE=[' + @DBNAME
                + '], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF';
            EXECUTE(@SQL);
        END

    IF NOT EXISTS ( SELECT  name
                    FROM    sys.database_principals
                    WHERE   name = @LoginName )
        BEGIN
            SET @SQL = 'CREATE USER [' + @LoginName + '] FOR LOGIN ['
                + @LoginName + ']';
            EXECUTE(@SQL);
        END

    IF EXISTS ( SELECT  name
                FROM    sys.server_principals
                WHERE   name = @LoginName )
        BEGIN
            EXEC sp_addsrvrolemember @LoginName, 'securityadmin'
        END
END
GO

GRANT EXECUTE ON [dbo].[usp_testproc] TO [appRole]
GO


sp_setapprole   @rolename='appRole',
                @password='Passw0rd!'



EXEC usp_testproc 'testlogin','password23@','stefaan'

Now for an example of option 2 I'd like to point out a answer a gave on another question: Msg 4834 “You do not have permission to use the bulk load statement”

I was going to just comment the link to the SQL Server 2012 Best Practice security white paper...but found out Microsoft took it off the Internet for some reason. You might be able to find a cached version of it some where online, but I will paste in the contents of contained database section of the document here.

There is also a BOL article for Security Best Practices with Contained Databases, this may be why the white paper was taken off but not sure.

Author of this whitepaper: Bob Beauchemin, SQLSkills; Published January 2012; Applies to SQL Server 2012

Contained Databases and Authentication

SQL Server 2012 introduced the concept of contained databases. A contained database is a database that includes all of the settings and metadata needed for its operation with dependencies on the instance. From a security perspective, a contained database makes it easier to have user accounts that are limited to a single database. SQL Server 2012 supports partially contained databases; it does not yet support full containment. Although contained databases allow more control by the “application administrator” they do have security repercussions.

Contained databases are interesting for a security point of view in that they allow defining users with authentication privileges, i.e. users who can log directly into a contained database without a corresponding login. Contained databases support two types of users: Windows users and groups that can directly connect to the database and do not need logins, and users with a password where the password is authenticated by the database, not the instance. This is not permitted by default; the instance administrator must specifically allow it by setting the “contained database authentication” configuration option on. To disallow connections to specific contained databases but allow access to other contained databases, a login trigger can be used.

Allowing direct connection of users to a database changes the effective threat-level of some existing permissions. For example, the permission ALTER ANY USER in a contained database gives permission to add user-based access to an instance through a contained database. Logins with ALTER ANY DATABASE or CONTROL DATABASE permission could also set containment on a database and add instance access through users. In addition, contained database users can connect to other database in the instance if the guest account is enabled.

Although you can use the system stored procedure sp_migrate_user_to_contained to migrate a database user mapped to a SQL login to a contained database user-with-password, contained databases’ user-with-password does not use password history and expiration policies as SQL logins do, although password complexity checks are still used. Therefore, attaching a contained database could allow weak passwords. In addition, the password hashes for these passwords are stored in the database, not in master. Anyone with access to the database file could perform a dictionary attack on a separate, unaudited instance.

There exists the possibility of conflicts, if a login and contained database user have the same name. The rule for resolving this conflict is that if an initial catalog is specified in the connection string and it’s the contained database, access is checking against the user based principal, not the login. To ameliorate this possibility, do not create conflicting names or specify a contained database as an initial catalog in a connection string. In addition, members of the sysadmin role should not use initial catalog in a connection string.

Best practices for contained databases

• Use the default (off) setting for contained database authentication and only turn this setting on if it is required.
• Protect backups of contained databases using passwords.
• Audit the capabilities of users and modules in contained databases.
• Audit logins that have the ability to set containment, if contained database authentication is allowed.
• Disable the guest account on databases that share an instance with contained databases.
• Take care to avoid login/user-with-login naming conflicts
• Avoid connection strings with initial catalog if contained database authentication is permitted.