This is nothing new but it’s still a fun idea.

The problem:

• The vendor app we just purchased (and are told we must keep) requires the sa password.
• The vendor insists that sa is hard coded into the app and can’t possibly be changed.
• The sa account is the built in super user for SQL Server and we certainly aren’t going to give it to an application.
• This might even be a shared server where giving one application sa means a security risk for all of the other applications.

A possible solution:

Did you know that sa can be renamed? It can. And that lets you do something like this:

ALTER LOGIN [sa] WITH NAME = [Not_sa];
GO
CREATE LOGIN [sa] WITH PASSWORD = 'StrongPassword', 
CHECK_POLICY = OFF;
GO
USE Test;
GO
CREATE USER [sa] FROM LOGIN [sa];
GO
ALTER ROLE db_datareader ADD MEMBER [sa];
ALTER ROLE db_datawriter ADD MEMBER [sa];
GRANT EXECUTE TO [sa];
GO

The user sa now only has read/write and execute permissions on a single database. So now you can give them the sa password with no risk.

There is a warning here though. Some patches require sa to be the actual sa. That means that before you patch you’ll have to make a point of switching back. Then switch back again when done.

Also I wouldn’t exactly call this a best practice but it’s certainly a fun trick.

Filed under: Microsoft SQL Server, Security, SQLServerPedia Syndication Tagged: microsoft sql server, security