sp_setapprole and creating users / logins

Question

We have set up SQL Server and our Application in such a way that an SQL Server login has … well no permissions when accessing the database using any tool, except our application. This is needed because some people might fire up access (or SQL Server Management Studio) and would be able to manipulate the data in that way.

So we set up an Application Role and gave that Application Role the necessary permissions. Everything works nicely, users can't access our tables unless they use our Application.

Now comes the problem. In our application it should be possible to Create additional logins / users, or set some specific users as being Administrators (only admins can do user related stuff). I'm not 100% certain but in that case I think the user needs SecurityAdmin role. So we have some code in our application which looks like this :

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

The problem is of course that the server_principals is in the Master Database, and there is no way I can give my Application Role those permissions. Is there any way to work-around this problem or an easy way to solve this ?

Well … actually my problem is I will need a good example on how to set this up I guess. For now I think I can set everything up with an AppUser application role and give that role all insert / select / update / execute rights for an application.

But what permissions should I give to the AppAdmin then in order to allow it to Create LOGINs and USERs on the SQL Server and allow them to set others as Admins and change other users passwords. Any suggestions on that, since that involves some stuff in the Master database which is outside of the scope of my Application Role. Or am I missing something?

With my current setup I'm getting : Msg 15151, Level 16, State 1, Line 1 Cannot alter the login 'test.gebruiker', because it does not exist or you do not have permission.

Answer 1

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”