1

As the title suggests I'm having problems changing the authentication mode from Windows to both SQL Server and Windows mode in version 12.0.2. I have tried right right-clicking the server, go to security and change it there but I'm getting an error when doing this. I installed it originally using both but changed it to windows only mode earlier this evening.

The error message I'm getting is long winded but the end of it is:

The EXECUTE permission was denied on the object 'xp_instance_regwrite', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error; 229)

Any idea what that means? I'm guessing I don't have the required permission but I'm not familiar enough with it to know what to do next. I have the sa password but can't enter it while I'm stuck in Windows mode.

I've been trying to figure out why I can't seem to write to the database all day and in desperation tried changing the mode to see if that would fix it... but it's only made things worse.

While connected using Windows credentials I tried ALTER LOGIN sa ENABLE also and was given the following error message in response:

Cannot alter the login 'sa', because it does not exist or you do not have permission.

I did try altering the registry too to change the mode but realised I was reading a post relating to SQL Server 2005

Thanks in advance for your help.

1

The EXECUTE permission was denied on the object 'xp_instance_regwrite', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error; 229)

You can fix the problem by granting the user direct access to the procedure.

USE master
GO

GRANT EXECUTE ON [sys].[xp_instance_regread] TO [DOMAIN\USER];

--To veriy that the user has been granted the privilege
EXECUTE AS USER = 'DOMAIN\USER';Select * from fn_my_permissions('xp_instance_regread','Object')

Cannot alter the login 'sa', because it does not exist or you do not have permission.

Error message saying that, You do not have administrator's permissions

Log into SQL Server Management Studio as an Administrator, because only admin user has permission to change passwords for users.

How to change sa user password see here: How to change a password for the sa user in MS SQL? and this link

  • Thanks for your help, I logged into the windows admin account and tried it that way and it worked. What stumped me was that I changed the type of the account I usually sign into windows with from a standard account to an administrator account yesterday but I guess it must still be missing some privileges or something. Panic over anyway, much appreciated! – DarrenG Oct 20 '18 at 10:26
0

Here is the solution:

Using SQL Server Management Studio

To change security authentication mode

  • In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.

  • On the Security page, under Server authentication, select the new server authentication mode, and then click OK.

  • In the SQL Server Management Studio dialog box, click OK to acknowledge the requirement to restart SQL Server.

  • In Object Explorer, right-click your server, and then click Restart. If SQL Server Agent is running, it must also be restarted.

To enable the sa login

  • In Object Explorer, expand Security, expand Logins, right-click sa, and then click Properties.

  • On the General page, you might have to create and confirm a password for the login.

  • On the Status page, in the Login section, click Enabled, and then click OK.

Using Transact-SQL

To enable the sa login

  • In Object Explorer, connect to an instance of Database Engine.

  • On the Standard bar, click New Query.

  • Copy and paste the following example into the query window and click Execute. The following example enables the sa login and sets a new password.

    ALTER LOGIN sa ENABLE ;
    GO
    ALTER LOGIN sa WITH PASSWORD = '' ;
    GO

For More Details Click Here

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.