The Last Pickle

It’s possible to Deny Connect permission to an Endpoint in SQL Server 2005. So I wondered if this would be a way disable the ’sa’ server account.

The sa account is disabled if the server only supports Windows security accounts, but is enabled if Mixed accounts are supported. Of course the recommendations are to use a very strong password, but it’s still there and open so I thought about disabling it.

To start with I tried to Deny the account permission to Connect to the TSQL/TCP endpoint on the server, which failed miserably.

deny connect on endpoint::[TSQL Default TCP] to sa

-- returns 
Cannot grant, deny, or revoke permissions to sa, dbo, 
entity owner, information_schema, sys, or yourself.

But it turns out there is another way to cripple the account.

alter login  [sa] disable

That will stop connections logging in using the sa account, it does not however stop code executing as the ’sa’ account.

execute as login = 'sa'

select 
    cast(suser_sname() as varchar(10)) as login_name, 
    cast(user_name() as varchar(10)) as user_name, 
    cast(entity_name as varchar(25)) as entity_name,
    cast(permission_name as varchar(20)) as permission_name
from  
    fn_my_permissions ( '[TSQL Default TCP]' , 'ENDPOINT')
    
revert

/*
login_name user_name  entity_name               permission_name
---------- ---------- ------------------------- --------------------
sa         dbo        [TSQL Default TCP]        CONNECT
sa         dbo        [TSQL Default TCP]        VIEW DEFINITION
sa         dbo        [TSQL Default TCP]        ALTER
sa         dbo        [TSQL Default TCP]        TAKE OWNERSHIP
sa         dbo        [TSQL Default TCP]        CONTROL
*/

There is another way to deny a connection to server by Denying ‘CONNECT SQL’ to an account. This Denies connection permission to the entire server, but as with Denying connection to a single Endpoint the permissions of the sa account cannot be modified.