0

When I create a new login in single-user mode I can see the user was created. When I restart the server and connect via SSMS the login does not exist.

Details:

I'm running MS SQLEXPRESS 2008 R2 and I don't have the sa password so I'm trying create a new login as a sysadmin member.

I start the service in single-user mode:

net start MSSQL$SQLEXPRESS /m"SQLCMD"

Then I connect via SQLCMD:

SQLCMD -S .\SQLEXPRESS -E

Then I run:

CREATE LOGIN newadmin WITH PASSWORD='1234'
GO
SP_ADDSRVROLEMEMBER newadmin, 'SYSADMIN'
GO

If I run:

SELECT * FROM master.sys.sql_logins

I see the new login has been created.

Then I quit SQLCMD, stop the service, then restart the service

net stop MSSQL$SQLEXPRESS
net start MSSQL$SQLEXPRESS

Then I connect via SSMS from another machine and refresh the tree view.

If I look at Security>Logins the new user is not listed.

If I restart the server in single-user mode again, and try to add the login again, it complains that the login already exists.

Does anyone have an idea of what might be wrong?

I've tried to connect to the database from SSMS while in single-user mode to confirm I'm working from the same database.

I've also connected to the database from SQLCMD while in multi-user mode and run select * from master.sys.sql_logins and can confirm the new login is not present.

It seems the new logins are only applied to the single-user instance? And my multi-user instance is different somehow?

3
  • 2
    What roles does the account have that you use to login when refreshing the tree view? Have you tried logging in as the new admin after restarting in multi-user mode?
    – HandyD
    Sep 9, 2021 at 3:04
  • 1
    Verify that SELECT @@SERVERNAME; returns the same name in single user mode and when you connect remotely.
    – Hannah Vernon
    Sep 9, 2021 at 3:26
  • @HandyD The account used to login where I do not see the new login has only the 'public' role. I did try logging in with the new login in mulit-user mode but I get a login failure (Error: 18456). After a little more digging, I see the server is set to allow Windows Logins only.
    – ebloohm
    Sep 9, 2021 at 14:46

1 Answer 1

1

The Server Role for the Login that could not see the newly created Login was "public".

Changing the Server Role for the Login to "sysadmin" allowed me to see the newly created Logins.

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.