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