Sql-server – minimize maintenance of duplicated Windows + SQL Auth logins

loginssql-server-2012users

In our environment, SQL authentication is the norm, but Windows auth is possible when users are using RDP. As a result, each physical user has two logical logins for each instance and two logical users for each database on each instance.

Is there any way to link two logins, so I can (e.g.) grant privs to both simultaneously?

This is a small thing, but seeing repetition like this triggers my "there's got to be a better way" sense:

CREATE LOGIN Bob
CREATE LOGIN Domain\Bob
ALTER SERVER ROLE serveradmin ADD MEMBER Bob
ALTER SERVER ROLE serveradmin ADD MEMBER Domain\Bob
USE Db;  CREATE USER Bob FOR LOGIN Bob
USE Db;  CREATE USER Domain\Bob FOR LOGIN Domain\Bob

Am I overlooking a feature that would reduce this duplication?

We'll eventually replace domain logins with AD groups, which will help.

Best Answer

I would suggest moving all clients to the domain and removing the SQL logins as the AD route is much more secure. If this is not an option then I would probably remove the AD logins and have RDP users use SQL logins as having two logins per user doesn't make much sense.

If for some reason you decide you have to go the two login route I would write a stored procedure to replicate the settings from AD users to your SQL logins. This could be triggered with a DDL trigger. This way you would only have to update one set of users.

I would also be careful in using AD groups if you are not the only person responsible for the administration of the domain. It's not clear from the database who has the permissions specified for the group as users be dropped in and out of the AD group without your knowledge.

Related Solutions

Sql-server – TFS 2012 – Database Project – User with NO LOGIN map to Windows – best practices

The idea of CREATE USER ... WITHOUT LOGIN isn't so you can map it later with ALTER USER ... WITH LOGIN (SQL Server simply refuses, as youhave found).

Instead, the idea is for application developers to change the security context before issuing SQL statements, by first doing EXECUTE AS USER = '<user>' WITHOUT REVERT to shed permissions.

This kind of gets you halfway to where you want to be. You ultimately still need to map some SQL Server logins to some SQL Database Users, but you reduce your need to know in advance what they will be, because you declare access-control permissions against the 'WITHOUT LOGIN' user.

See:

SQL Server – Using sp_setapprole for Creating Users and Logins

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:

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")
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”

Best Answer

Related Solutions

Sql-server – TFS 2012 – Database Project – User with NO LOGIN map to Windows – best practices

SQL Server – Using sp_setapprole for Creating Users and Logins

Related Question