Link to home
Start Free TrialLog in
Avatar of HKFuey
HKFueyFlag for United Kingdom of Great Britain and Northern Ireland

asked on

SQL user exists in database but not in security SQL Server 2019

If I look at user properties it says "SQL user without login"

I have tried this which has worked in the past:
USE Master
EXEC sp_change_users_login 'Auto_Fix', 'MY_SQL_USER'
I get:
The number of orphaned users fixed by updating users was 0.
The number of orphaned users fixed by adding new logins and then updating users was 0.


So I tried this:
USE MyDatabase
EXEC sp_change_users_login 'Auto_Fix', 'MY_SQL_USER'
I get:
Msg 15600, Level 15, State 1, Procedure sp_change_users_login, Line 237 [Batch Start Line 0]
An invalid parameter or option was specified for procedure 'sys.sp_change_users_login'.
Avatar of Amir Azhdari
Amir Azhdari
Flag of United States of America image

That error means a SQL Login does not exist for that database user and it must be created.
Avatar of HKFuey

ASKER

I can see the user in Security under my DB, just not at the top level of SSMS.
Avatar of HKFuey

ASKER

OK I added in the user at the top level then ran:

   USE MyDatabase
   EXEC sp_change_users_login 'Auto_Fix', 'MY_SQL_USER'

This returned:
The number of orphaned users fixed by updating users was 1.
Brilliant- So means it has resolved your problem?
Avatar of HKFuey

ASKER

The user I fixed I knew the password for the others I don't.
If you don’t know the password you are potentially out of luck I'm afraid. Unless you log in as sa and change their passwords first which will affect their logins.
ASKER CERTIFIED SOLUTION
Avatar of Darran Brown
Darran Brown

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial