Login fails for Users Created for a login

Question

	-- in master
	IF not exists(SELECT * FROM sys.sql_logins  WHERE name = 'lgnApp')
	BEGIN
		CREATE LOGIN lgnApp WITH PASSWORD = 'Appuser_123';
	END

	-- For each database
	IF not exists(SELECT * FROM sys.database_principals  WHERE name = 'AppUser')
	BEGIN
		CREATE USER AppUser for login lgnApp WITH DEFAULT_SCHEMA = dbo;
	END

Answer 1

Look in the SQL Server errorlog, where you will get details on why the login failed.

Answer 2

Hi @Mike Toubasi

How about following below steps- (assuming you are Creating SQL Login and SQL User on your Azure SQL DB)
/1: Create SQL Login on master database (connect with admin account to master database)/

CREATE LOGIN MaryLogin WITH PASSWORD = '<strong_password>';

/2: Create SQL user on the master database (this is necessary for login attempt to the <default> database, as with Azure SQL you cannot set the DEFAULT_DATABASE property of the login so it always will be [master] database.)/

CREATE USER MaryUser FROM LOGIN MaryLogin;

/3: Create SQL User on the user database (connect with admin account to user database)/

CREATE USER MaryUser FROM LOGIN MaryLogin;

/4. Grant permissions to the user by assign him to a database role/

ALTER ROLE db_datareader ADD MEMBER MaryUser;

Thank you!

Answer 3

Hi @Mike Toubasi,

Check with below code.

IF not exists(SELECT * FROM sys.sql_logins  WHERE name = 'lgnApp')
	BEGIN
		CREATE LOGIN lgnApp WITH PASSWORD = 'Appuser_123';
	END

CREATE USER AppUser for login [lgnApp];
GRANT CONTROL ON schema::[dbo] TO [AppUser];

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.