1

I found out that on an instance of a certain SQL server at my company- sa is virtually the only login used by all the sessions. The SPID# extends to 130. What do you think is the impact of this? Is there any downside despite of course security? Appreciate you pitching in on this. Thanks.

2
  • 3
    Do you need a downside other than security? You have ~130 sessions, all with full and complete control over your entire instance and potentially the machine as well. Jul 29, 2016 at 12:41
  • 2
    Security is the most important and biggest downside you face with that. It's best SQL practice to grant the least amount of permissions to any app, so long as the app can function. Would you give your entire neighborhood the keys to your house?
    – Jason B.
    Jul 29, 2016 at 12:52

2 Answers 2

6

The impact is a very severe security risk. It might be that your application is hard-coded to use 'sa'; often very poorly written and implemented 3rd party apps do this kind of awful thing.

The question you need to ask is, "what can I do to fix this?", because it definitely needs fixing urgently.

If it is a 3rd party app, you may need to escalate to your support provider.

Ideally you will create a new database role, assign minimum required permissions to that role [e.g. exec on certain Stored Procedures] , then create logins/groups for your users, add them to this role, and modify the application's connection settings to use their own logins instead of sa.

You should also then create a new sysadmin login NOT called 'sa', for your own use, and then if possible, remove sa itself from the sysadmin fixed server role, and/or disable it.

Having all your users connect using a sysadmin-privileged login (even if it is one other than sa), is, to put it mildly, far from ideal.

3
  • 1
    If the application is hard-coded to use sa, you might be able to rename the sa account to something else, then create a non-privileged account named sa and test what permissions are actually necessary. Even if you end up giving it full db_owner access to the application backend, it would still be better than having it connecting with a sysadmin account. Jul 29, 2016 at 13:26
  • Also, if you rename it, check your agent jobs and make sure that any ones owned by sa get their owner changed to your new, renamed version. There's definitely some considerations when you rename sa on an active server. I've also heard grumbling that upgrades sometimes fail if the sa account is renamed, disabled or doesn't have sysadmin authority. Jul 29, 2016 at 13:30
  • 1
    Yes, hence by retaining it, but removing it from sysadmin, you can always simply temporarily re-add it to sysadmin if/when required for upgrades etc. Also you don't get the issue with job ownership you alluded to. I'd also reset the sa password as matter of urgency either way. Jul 29, 2016 at 13:32
2

I had a server with a similar problem. What I did was to trace the sa account, use the results to determine the permissions needed, then create a new account named SystemAdministrator with the same password and grant the permissions to the new account. Then I switched the names of the two accounts so that the SA account was a regular user account and removed SA from the sysadmin role and changed the password for SystemAdministrator. I also tracked down all of the applications configured to connect with the SA login, which can be identified using the same trace. Once these applications are located, you can add ";Application Name =app1" to the end of the connection string to separate permissions by application because it will be sent to the server as application name or program name.

Sessions with a SPID below 50 are generally sessions needed by the database engine to run the server. After switching the login names, you will see that those run under SystemAdministrator. Database owners and SQL Agent job owners automatically changed since the owner sids are saved in the system tables. Refresh any SSMS Object explorer connection before verifying.

First, create the trace by going into Profiler or Extended Events and add a column filter for SA. Under Security Audit, you will want to trace the event Audit Database Object Access Event. Run this as a server side trace and specify that the file go onto a large drive. Even though the SA login bypasses the permission check algorithm, it still saves the permissions necessary to execute the statement under a column named permissions in the trace. The following script will use the profile trace file to generate the sql commands necessary to grant permissions to the SystemAdministrator account:

declare @filepath varchar(1000)

SELECT 
     @filepath=cast(value as varchar(1000))
FROM sys.fn_trace_getinfo(5) -- replace this number with your traceid
where traceid=5 and property=2

-- creates the necessary users in the databases
select distinct 'create user' as permission_desc, DatabaseName, 'use [' + DatabaseName + '];
if not exists(select * from sys.database_principals where name =''SystemAdministrator'')
    CREATE USER [SystemAdministrator] FOR LOGIN [SystemAdministrator]' as SQlStatement
from (
select
    case
        when permissions =1   then 'SELECT'
        when permissions = 2  then 'UPDATE'
        when permissions = 4  then 'REFERENCES'
        when permissions = 8  then 'INSERT'
        when permissions = 16 then ' DELETE'
        when permissions = 32 then ' EXECUTE'
        when permissions = 4096  then 'SELECT ANY (at least one column)'
        when permissions = 8192  then 'UPDATE ANY'
        when permissions = 16384 then ' REFERENCES ANY'
        when permissions is null and left(cast(textdata as varchar(max)),8)='Truncate' then 'Alter Table'
    end as permission_Desc,
    * 
from fn_trace_gettable ( @filepath , 1 )
where DatabaseID<>32767 and XactSequence<>0 and DatabaseName not in ('master','tempdb','model','msdb') and permissions =1
) data1

-- grant granular permissions to SystemAdministrator

select distinct permission_desc,  ObjectName,   DatabaseName, OwnerName,'use [' + DatabaseName  + '];grant ' +  permission_desc collate database_default + N' on [' +
    DatabaseName  + N'].['  +   OwnerName  + N'].[' +   ObjectName +N'] to [SystemAdministrator]'  SQLStatement
from (
select
    case
        when permissions =1   then 'SELECT'
        when permissions = 2  then 'UPDATE'
        when permissions = 4  then 'REFERENCES'
        when permissions = 8  then 'INSERT'
        when permissions = 16 then ' DELETE'
        when permissions = 32 then ' EXECUTE'
        when permissions = 4096  then 'SELECT ANY (at least one column)'
        when permissions = 8192  then 'UPDATE ANY'
        when permissions = 16384 then ' REFERENCES ANY'
        when permissions is null and left(cast(textdata as varchar(max)),8)='Truncate' then 'Alter Table'
    end as permission_Desc,
    * 
from fn_trace_gettable ( @filepath , 1 )
where DatabaseID<>32767 and XactSequence<>0 and DatabaseName not in ('master','tempdb','model','msdb') and (permissions <>1 or (permissions is null and left(cast(textdata as varchar(max)),8)='Truncate' ))
) data1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.