SQL Server - Adding new level of security above 'sa' user.

Asked By lcsila

25-Jan-10 10:54 AM

Hi all,

Our client has asked us a good question:

We need to add security to our sql server management tools in our cliente
servers. All of them share the same password for the user 'sa'. Also, all the
applications in production use 'sa' in their connection strings. The password
of 'sa' user is almost public (everybody knows it).

The cliente asked us a way to use a new level of security for our management
tools so nobody can log on the sql server and backup any data... WITHOUT
changing SA password.

We can??t also use OS users security, cause the servers must have public
access. We are looking for some sort of application that works between SO and
Sql Server management tools.

That kind of application/software exists?...  Someone has any idea of what
can we do?.

Thanks in advance,

Regards,

26-Jan-10 04:12 AM

lcsila
You can disable sa login, however your applications will not work then....
SA has high permission (sysadmin) and you cannot create something above:-)

26-Jan-10 11:12 PM

lcsila (u57759@uwe) writes:

I am afraid that I do not understand the last paragraph. Why would you
have to change the SA password just in order to take a backup.

In any case, in your current situation, you have zero security. If everyone
knows the SA password, everyone can do anything.

I would suggest that you make it a high priority to change this. That is,
stop using sa in connection strings, review which permission you
actually need etc.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

28-Jan-10 08:09 AM

You may create another account with sysadmin rights, and then use this
account to "downgrade" sa to something lower.
I do not know whether downgrading sa has an impact on SQL Server or not, but
I do not see why this would not work.

pher

30-Jan-10 12:43 AM

Won't work.  While you can 'remove' permission from the sa account, anyone
with that sa account can add them back at will.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Blog/web site: http://coding-paparazzi.sylvainlafontaine.com
Independent consultant and remote programming for Access and SQL-Server
(French)

03-Feb-10 09:04 AM

Oooops, you are right ! My apologies.
Then... I fear there is no solution without changing the sa password.

Pierrot