One of my all-time favorite things in SQL Server is security. No matter what, it always seems that there is a new way to abuse permissions. When people abuse their access level or abuse the way permissions should be set in a SQL Server environment, we get the pleasure of both fixing it and then trying to educate them on why what they did was wrong and how to do it the right way.

In similar fashion, I previously wrote about some fundamental misconceptions about permissions here and here. I have to bring those specific articles up because this latest experience involves the basics discussed in those articles along with a different twist.

I do hope that there is something you will be able to learn from this basics article. If you are curious, there are more basics articles on my blog – here.

Gimme Gimme Gimme…

It is not uncommon to need to create a login and grant that login access to a database (or associate that login to a database user. In fact, that is probably a fairly routine process. It is so routine, that I have a demo script for it right here.

USE [master]
GO
IF EXISTS(SELECT 1/0 FROM sys.databases WHERE name = 'GimmeSA')
BEGIN
DROP DATABASE GimmeSA;
END
CREATE DATABASE [GimmeSA];
IF NOT EXISTS (SELECT 1/0 FROM sys.server_principals WHERE name = 'IMustHaveSA')
BEGIN
SELECT 'IMustHaveSA Does NOT Exist - Create the login'
CREATE LOGIN [IMustHaveSA] WITH PASSWORD=N'weakpassword', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
END
USE [GimmeSA]
GO
CREATE USER [IMustHaveSA] FOR LOGIN [IMustHaveSA]
GO
USE [GimmeSA]
GO
ALTER ROLE [db_accessadmin] ADD MEMBER [IMustHaveSA]
GO
USE [GimmeSA]
GO
ALTER ROLE [db_backupoperator] ADD MEMBER [IMustHaveSA]
GO
USE [GimmeSA]
GO
ALTER ROLE [db_datareader] ADD MEMBER [IMustHaveSA]
GO
USE [GimmeSA]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [IMustHaveSA]
GO
USE [GimmeSA]
GO
ALTER ROLE [db_ddladmin] ADD MEMBER [IMustHaveSA]
GO
USE [GimmeSA]
GO
ALTER ROLE [db_owner] ADD MEMBER [IMustHaveSA]
GO
USE [GimmeSA]
GO
ALTER ROLE [db_securityadmin] ADD MEMBER [IMustHaveSA]
GO

I even went as far as to include some of the very routine mistakes I see happening on a frequent basis (as referenced by a prior post here).

To this point, we only have a mild abuse of how to set permissions for a principal. Now it is time for that twist I mentioned. This user account needs to be created on a secondary server that is participating in either a mirror or an Availability Group. Most people will take that user account that was just created on the first server and then use the same script to add the account to the secondary server. Let’s see how that might look.

For this example, I will not go to the extent of creating the mirror or AG. Rather, I will pretend I am just moving the database to a new server. So I have taken a backup and then I will restore the database to the new server.

USE [master]
RESTORE DATABASE [GimmeSA] FROM  DISK = N'C:\Database\GimmeSA.bak' 
WITH  FILE = 1
,  MOVE N'GimmeSA' TO N'D:\SQL16\Data\GimmeSA.mdf'
,  MOVE N'GimmeSA_log' TO N'D:\SQL16\Log\GimmeSA_log.ldf',  NOUNLOAD,  STATS = 5, replace
GO

Next, let’s go ahead and recreate the login we created on the previous server.

IF NOT EXISTS (SELECT 1/0 FROM sys.server_principals WHERE name = 'IMustHaveSA')
BEGIN
SELECT 'IMustHaveSA Does NOT Exist - Create the login'
CREATE LOGIN [IMustHaveSA] WITH PASSWORD=N'weakpassword', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
END

You see here that I am only going to create the login if it does not exist already. Running the script produces the following for me.

Now, let’s deviate a bit and grant permissions for the login just like so many administrators will do.

It seems pretty apparent that my login that I just created does not have access to the GimmeSA database, right? Let’s go ahead and add permissions to the GimmeSA database and see what happens.

Well, that did not work according to plan right? Enter twist the second.

What I am seeing more and more of, is people at this point will just grant that login (that was just created) sysadmin rights. You can pick up your jaw now. Indeed! People are just granting the user SA permissions and calling it good. This practice will certainly work – or appear to work. The fact is, the problem is not fixed. This practice has only camouflaged the problem and it will come back at some future date. That date may be when somebody like me comes along and starts working on stripping non-essential sysadmins from the system.

There are two legitimate fixes for this particular problem (and no granting sysadmin is definitely not one of them). First you can run an orphan fix with a script such as this one by Ted Krueger. That will map the user that already exists in the database to the login principal (thus the reason for the error we saw). Or, you can prep your environment better by using the SID syntax with the create login as follows.

CREATE LOGIN TestLogin WITH PASSWORD = 'SuperSecret52&&'
, SID = 0x241C11948AEEB749B0D22646DB1A19F2;

The trick here is to go and lookup the SID for the login on the old server first and then use that sid to create the login on the new server. This will preserve the user to login mappings and prevent the orphan user issue we just saw. It will also prevent the band-aid need of adding the login to the sysadmin server role.

The Wrap

In this article I have introduced you to some basics in regards to creating and synchronizing principals across different servers. Sometimes we try to shortcut the basics and apply band-aids that make absolutely no sense from either a practical point of view or a security point of view. Adhering to better practices will ease your administration burden along with improving your overall security presence.

This has been another post in the back to basics series. Other topics in the series include (but are not limited to): Backups, backup history and user logins.