question

Donald-0878 avatar image
0 Votes"
Donald-0878 asked TomPhillips-1744 commented

Cannot Alter Login

I tried create new login password for "sa".
After expanding Security and Logins, the list of users were displayed on the left as shown in the screenshot below. But what I dont understand is,
how come I have other user listed? I only created one user for the database, but I have 4 on the list:
cashgolds, quirverT, sa andTRVAL17
I only created "quirverT"*
and in the screenshot, its only "sa" that has a red "X". and I tried to grant permission to "sa" but I got the error in the screenshot.
Ihave tired everything to grant permission to that user but seem not to get it right.

What surprises mw as well is the fact that the error I am getting while trying to create new account in my website has to do with user 'quirverT'

202037-login-failed.png



sql-server-generalsql-server-transact-sql
login-failed.png (132.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Cathyji-msft avatar image
0 Votes"
Cathyji-msft answered

Hi @Donald-0878,

how come I have other user listed? I only created one user for the database, but I have 4 on the list:

Someone created these logins for some purpose. Such as created these logins for other peoples to connect to this SQL server instance.

and in the screenshot, its only "sa" that has a red "X". and I tried to grant permission to "sa" but I got the error in the screenshot.

The sa login, short for system administrator, is one of the riskiest server-level principals in SQL Server. It’s automatically added as a member of the sysadmin fixed server role and, as such, has all permissions on that instance and can perform any activity. SQL Server by default, at installation, disables the 'sa' account. In fact, unless a connecting system absolutely requires the sa login, it’s best that the account remains disabled. Why did you want to enable ‘sa’ account?

SA is system admin user and it is the highest level of user in system. If any user have to modify SA that user needs to have higher or equivalent rights as SA user. Users member of system admin group are can only change SA user. According to the error message, it seems you do not have the permission.


If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TKujala avatar image
0 Votes"
TKujala answered TomPhillips-1744 commented

Hi @Donald-0878,

Have you tried this?

 Open Sql Configuration Manager.
 Select SQL Server Services.
 On the right side, Select the instance.
 Right click on it and open Properties.
 In the Advanced tab attach ";-m" at the end of the Startup Parameters field.
 Apply and restart the service.
 Now you have privilege to enable "sa" user and modify its password.
 Once done, remove ";-m" and restart the service.

More.


· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

By selecting instance, you mean this?
201938-sql-config.png


0 Votes 0 ·
sql-config.png (42.0 KiB)

@Donald-0878,

By selecting instance, you mean this?

Yes, that's right.

0 Votes 0 ·

Parameter is Invalid; that's the error message I'm getting

0 Votes 0 ·

All of this is because I uploaded my files and imported my database and tried to create a new account on my website but I couldn't due to this error in the screenshot below. It's been really difficult trying to resolve this; i have tried over about 11 days without solution.
It say login failed for user quirverT, but when I checked my SSMS there is no red 'X' marked on the user "quirverT", except for "sa"

Here is the error
202038-login-server.png


0 Votes 0 ·
login-server.png (90.2 KiB)

That error does not have anything to do with the user "sa".

What do you mean you "imported your database"? Did you restore a backup? When you copy a database between servers, logins do not get transferred. You would need to recreate the login for quirverT and then link it to the database "user".

See: https://www.mssqltips.com/sqlservertip/1590/understanding-and-dealing-with-orphaned-users-in-a-sql-server-database/

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

The option -m as suggested by TKujala would set the SQL Server instance into single-user mode. I would not recommend that.

Also, I am not sure that you are working with right instance. The screenshot you show seem to be from your local machine. But I seem to recall from another thread of yours, that the name you have in Object Explorer is with a hosting service, and thus this SQL Server instance runs on a computer at your hoster. To change the startup options, you would have to connect to this computer with Remote Desktop to run SQL Server Configuration Manager. SSCM only works with services on the local computer.

However, I would not expect that your hosting service permit you to connect with Remote Desktop, and if they do, I wold not expect that you have Windows permissions to run SSCM.

To continue on this theme, my assumption why you cannot change the password for sa is exactly what the error message says: you don't have permission. That is, your hoster is not giving you sysadmin access on the instance. I would also assume that the logins you see and you don't recognize have been create by your hoster for some purpose.

I would recommend that you contact the support desk of your hosting service to get more clarity about this.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.