All the articles over the years tell me that disabling sa wont affect any jobs owned and run by sa. I'm running the SSMS builtin Maint wizard job: "Misc Cleanup.Subplan_1" and "DB Integrity Check.Subplan_1" and also a Ola Hallengren "Index Optimize and stats" job. All of them fail since I disabled the sa account. If I re-enable sa the jobs all work. Message:\ >Executed as user: NT Service\SQLSERVERAGENT. \ \ Microsoft (R) SQL Server Execute Package Utility Version 15.0.4420.2 for 64-bit Copyright (C) 2019 Microsoft. All rights reserved. \ \ Started: 11:08:30 AM Error: 2025-03-17 11:08:30.84 Code: 0xC00291EC Source: {D9910D3F-29BA-4AFB-9499-EB09B2E4F937} Execute SQL Task Description: Failed to acquire connection "Local server connection". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error\ \ Warning: 2025-03-17 11:08:30.84 Code: 0x80019002 Source: OnPreExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning\ \ Error: 2025-03-17 11:08:30.86 Code: 0xC0024104 Source: History Cleanup Task Description: The Execute method on the task returned error code 0x80131904 (Login failed for user 'sa'. Reason: The account is disabled.). The Execute method must succeed, and indicate the result using an "out" parameter. End Error \ \ Warning: 2025-03-17 11:08:30.86 Code: 0x80019002 Source: Subplan_1 Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning \ \ Error: 2025-03-17 11:08:30.86 Code: 0xC0024104 Source: {229E238E-F0DA-453A-AB7A-027524E534C9} Description: The Execute method on the task returned error code 0x80131904 (Login failed for user 'sa'. Reason: The account is disabled.). The Execute method must succeed, and indicate the result using an "out" parameter. End Error\ \ Warning: 2025-03-17 11:08:30.86 Code: 0x80019002 Source: OnPostExecute Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning \ \ DTExec: The package execution returned DTSER_FAILURE (1). \ \ Started: 11:08:30 AM Finished: 11:08:30 AM Elapsed: 0.422 seconds. The package execution failed. The step failed.

I was looking for the last time users have been logged into the system and saw SA as being used. Strange is that SA is disabled by default and not used on our systems. How is that possible, what am I missing? The query used to list users: SELECT login_name [Login] , MAX(login_time) AS [Last Login Time] FROM sys.dm_exec_sessions GROUP BY login_name; The result with SA listed as disabled and logged in:

Is there a way to create a sysadmin like account with only read privilege's. I know that you can give a lot of grants to an account like view server state, view any definition, view any database etc, etc.. but still than this user will not always return the same info as the real SA user. In some system procedures there are checks implemented to see if you are sysadmin. It would be handy to have an account that can do all what you can do with the SA account except modifying anything. Is that possible?

I have installed SQL Server Express 2019 on my machine and after installation, I am able to login into server using the Windows Authentication method like And as you can see the sa account has been disabled by default So I tried to enable the sa account like But I am getting this error message > Cannot alter the login 'sa', because it does not exist or you do not have permission.(Microsoft SQL Server, Error:15151) Even when I tried to change the server authentication mode from Windows Authentication mode to SQL Server and Windows Authentication mode at I am facing this error! > The EXECUTE permission was denied on the object 'xp_instance_regwrite', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error; 229) So it looks like I have no/not granted any permission to do any anything! when login with Windows which apparently is my only option to get into server!(this is also happening when I tried to create new user!) Can you please let me know what I am doing wrong and how I can handle this to act as an administrator for DB when I login? I have already seen and tried this post , so please kindly, if you are not sure about the solution do not close, down vote or redirect me again to this post?

I can log onto Sql Server Management Studio without issues. I can't create a new DB however as it appears that I don't have permissions. I can't give myself more permissions I can't modify the SA login. I thought my default my windows BUILTIN account would have access. What am I missing? Thank you

I changed sa and Windows administrators password successfully, I can login to server with sa without any problem, services working normally, database is not corrupted and working without any problem. Problem is I cannot login with Windows authentication after changing sa password, mixed login mode is enabled, even I can create new user and I can login with it but I can not login with Windows authentication, the error I get is always:

Number: 18456 Severity: 14 State: 1 Line Number: 65536

Another thing is I can connect to Analysis services with Windows authentication, but Database Engine does not accept Windows authentication. info:

@@Version

: > Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: ) (Hypervisor) SSMS is checked to run as Administrator always, but does not help.

Apologies in advance if I don't use some terminology correctly here. I'm not a DBA but I've been temporarily forced into the role. I've inherited an application that runs on SQL Server 2016 Enterprise - one instance for PROD and another for TEST. These instances are set up in an "always on" configuration, so I have one Availability Group for PROD and one for TEST. Each Availability Group has two servers in it. Each instance has multiple databases in it, but only one DB in each instance is actually used by the application - the others look to have been created for testing purposes. I'm only mentioning that there are other DBs just in case it might be relevant in some way that I'm not aware of. I have a login for a service account (Windows authentication), and that login is mapped to a user of the same name in a few of the DBs. This service account is a member of the sysadmin role. After an IT Security audit, I've been told to remove SA rights from this account. I don't think this will cause any problems for the application, but of course I want to remove the rights in the TEST instance first so I can confirm that the application doesn't need them for some reason. In the TEST instance, I've unchecked the sysadmin role for the login and it now only has the "public" box checked, but I find that I can still log in as the service account and access everything that it had access to before. It can still access all the tables in the main database (which I expected because the account has been granted a lot of rights on the dbo schema in the database), but the confusing thing to me is that it can still view the jobs at the server level, which I thought it would lose. If I run this statement (SELECT * FROM fn_my_permissions(NULL, 'server');) while logged in as the service account, this is the list of permissions that I get back: - CONNECT SQL - SHUTDOWN - CREATE ENDPOINT - CREATE ANY DATABASE - CREATE AVAILABILITY GROUP - ALTER ANY LOGIN - ALTER ANY CREDENTIAL - ALTER ANY ENDPOINT - ALTER ANY LINKED SERVER - ALTER ANY CONNECTION - ALTER ANY DATABASE - ALTER RESOURCES - ALTER SETTINGS - ALTER TRACE - ALTER ANY AVAILABILITY GROUP - ADMINISTER BULK OPERATIONS - AUTHENTICATE SERVER - EXTERNAL ACCESS ASSEMBLY - VIEW ANY DATABASE - VIEW ANY DEFINITION - VIEW SERVER STATE - CREATE DDL EVENT NOTIFICATION - CREATE TRACE EVENT NOTIFICATION - ALTER ANY EVENT NOTIFICATION - ALTER SERVER STATE - UNSAFE ASSEMBLY - ALTER ANY SERVER AUDIT - CREATE SERVER ROLE - ALTER ANY SERVER ROLE - ALTER ANY EVENT SESSION - CONNECT ANY DATABASE - IMPERSONATE ANY LOGIN - SELECT ALL USER SECURABLES - CONTROL SERVER Those seem like SA-level permissions to me, but I don't understand where they're coming from since the sysadmin role is no longer applied to the login. I thought that they might have been granted individually to the login (which seems weird to me, because doesn't that just bypass the purpose of having roles in the first place?) so I tried revoking them. The revoke commands appear to be successful ("Commands completed successfully"), but when I run the same command to check the rights from the service account again, it still has all the same permissions. I've also found that while logged in as the service account, it can assign the sysadmin role to itself (and remove it from itself). Other notes: every time I make changes to the account, I'm careful to make sure that I'm making the same changes on both servers that are part of the Availability Group. My understanding is that the logins are separate on each server even though they map to the same database-level user, so any changes to the login have to be done on both servers. Another thing I've tried is removing the sysadmin role from the account on the secondary node of the PROD instance just to see if I have the same problem with the permissions getting "stuck" on, and the issue doesn't happen there. I feel like I'm missing something obvious here, but I don't know what it is. Any help would be much appreciated.