Login failed for new user SQL Server

Question

I am trying to create a user that only has access to a few views and procedures. The user seems to be created fine, but when I attempt to log in to the user account using the connection string below, I get the error Login failed for user 'Interface_Admin'.

User ID=Interface_Admin;Password=letmein01;Integrated Security=False;server=SQL02;database=TESTDB;Trusted_Connection=False;

My code to create the user and grant permissions is below.

USE TESTDB
GO

IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'Interface_Admin') DROP USER Interface_Admin
IF EXISTS (SELECT * FROM master.dbo.syslogins WHERE name = 'Interface_Admin_Login') DROP LOGIN Interface_Admin_Login
IF EXISTS (SELECT * FROM sys.database_principals WHERE name = N'Interface_Users') DROP ROLE Interface_Users

-- Create role and add user accounts
CREATE ROLE Interface_Users
CREATE LOGIN Interface_Admin_Login WITH PASSWORD = 'letmein01'
CREATE USER Interface_Admin FROM LOGIN Interface_Admin_Login
EXEC sp_addrolemember @rolename='Interface_Users', @membername='Interface_Admin'
GO

-- Grant permissions
-- Views
GRANT SELECT ON Vw_Interface_Main TO Interface_Users;

-- Procedures
GRANT EXECUTE ON Proc_GetNextFileSequence TO Interface_Users;
GRANT EXECUTE ON Proc_OutboundFiles TO Interface_Users;
GRANT EXECUTE ON Proc_InsertOrUpdateFile TO Interface_Users;

@dfundako not entirely sure what you mean there. I take it that's a setting in SQL Server somewhere? — ryansin
– ryansin, Commented Mar 19, 2018 at 17:10
Run this: SELECT CASE SERVERPROPERTY('IsIntegratedSecurityOnly') WHEN 1 THEN 'Windows Authentication' WHEN 0 THEN 'Windows and SQL Server Authentication' END as [Authentication Mode] — dfundako
– dfundako, Commented Mar 19, 2018 at 17:11
In SSMS, right-click the SQL Server node at the top, select properties, then click on the "Security" tab, and ensure SQL Server authentication mode allows both Windows and SQL Server logins. — pmbAustin
– pmbAustin, Commented Mar 19, 2018 at 17:12

Gareth Lyons · Accepted Answer · 2018-03-21 14:00:24Z

1

Your original script has Create Login with Interface_Admin_Login, but then User Id in the connection string is Interface_Admin.

Logins should be done with the Login name, not user name. Login = access to the instance, users are used to handle permissions within each database. Generally users are created with the same name as the login to avoid confusion.

answered Mar 21, 2018 at 14:00

Gareth Lyons

1,97214 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ryansin · Accepted Answer · 2018-03-20 10:18:51Z

0

So, I think this is down to a misunderstanding on my part, but I was able to resolve this by giving the same name to both the user and the login.

-- Create role and add user accounts
CREATE ROLE Interface_Users
CREATE LOGIN Interface_Admin WITH PASSWORD = 'letmein01'
CREATE USER Interface_Admin FROM LOGIN Interface_Admin
EXEC sp_addrolemember @rolename='Interface_Users', @membername='Interface_Admin'
GO

answered Mar 20, 2018 at 10:18

ryansin

1,8573 gold badges28 silver badges54 bronze badges

2 Comments

Gareth Lyons Over a year ago

Yep, you need to login using the login name. Login = access to the instance, then users are used to handle permissions within each database. Generally you'll create users with the same name as the login to avoid confusion.

ryansin Over a year ago

Ok, thanks for elaborating, that explains a lot. If you phrase that as an answer then I'll mark it as best answer.

Collectives™ on Stack Overflow

Login failed for new user SQL Server

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related