I'm looking at a SQL 2012 server that someone else did the install/config on. They did select Mixed-mode security, but there is no sa account in the server-level logins.
There are several servers on our network that have this going on. These servers treat the person who did the install as the sa account. In other words this person has to be the owner of everything and we will never be able to disable his account.
How can I change this? How can I create a legitimate sa login and make it be everything that it's supposed to be by way of permissions and ownerships. I know how to create the named account, but not what all it should have to function as expected.
October 4, 2019 at 3:02 pm
Do you yourself have privileged access to the server? If not, you won't be able to see the sa login, even if it exists. As far as I know, the sa login can't be dropped, although it can be disabled or renamed. If you run SELECT * FROM sys.server_principals WHERE prinicpal_id = 1, you'll get the details for sa.
John
October 4, 2019 at 3:09 pm
Yes, I have local admin on the server and sysadmin in the SQL instance.
principal_id = 1 is a named login belonging to a 3rd party vendor. Prior to my hiring, nobody in the company knew anything about DBA-ing, so they got into a really bad habit of letting vendor employees in their systems to do installs, configs, customs, etc. for them. Like with no documentation of what they did or anything. It's as horrific as it sounds.
The server in question is one that's not actively being used yet, but they want to rename it and put it into production to replace another server. This whole plan/project started before me and is seen as too far to turn back now. I'm hoping to fix what I can on it before they "flip the switch".
To answer your original question, sa has permission to do everything in SQL Server (if anyone knows something it can't do, please don't flame me). Best practice is to rename it, set a password that nobody has access to, and don't use it except in a dire emergency. Create a Windows group that contains DBAs and give that group sysadmin access to SQL Server. Monitor the membership of the group for unauthorised additions. Create SQL logins for applications or application service accounts access, with only the privileges they need for applications to run. Build test servers and then revoke access for developers and third parties from the live server. I could go on, but that should get you started!
John
October 4, 2019 at 3:25 pm
Login with sid = 0x01 from sys.logins is the original 'sa' account that gets created during install and this cannot be dropped. In your case, They might have just renamed 'sa' login to whatever name you are seeing now. Renaming and disabling 'sa' account is a very common practice in many shops for security reasons.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply