Skip to main content
Share Your Experience: Take the 2024 Developer Survey
18 votes

Determine whether mixed mode auth is enabled without logging in?

You can check it in the registry: HKLM\Software\Microsoft\Microsoft SQL Server\ [instancename] \MSSqlServer The current mode is recorded in the LoginMode key. Values (DWORD) can be: 1 = Windows ...
Julien Vavasseur's user avatar
17 votes

How to deny access to SQL Server to certain login over SSMS, but allow over .Net SqlClient Data Provider

I think there is no reliable solution for your problem since Application Name is modifiable parameter that cam be changed by any user. Here is how to change it within SSMS: In Connect to Database ...
sepupic's user avatar
  • 11k
17 votes
Accepted

How can I fix "Login failed for user sa" error in SQL Server?

In SSMS, right-click on the server and go to Properties and under the security page, check what Server authentication looks like. Odds are it's set to Windows Authentication only and needs to change ...
Antoine Hernandez's user avatar
15 votes
Accepted

Determine whether mixed mode auth is enabled without logging in?

Another approach is to try logging in, using SQL authentication, with an obviously fake account that won't succeed. You can then use notepad to open up the ERRORLOG file in C:\Program Files\Microsoft ...
Aaron Bertrand's user avatar
13 votes
Accepted

How to lock a sql login after N unsuccessful login attempts

Yes, you can but you have to set everything up. SQL Server (SQL) Login When you enable the Enforce password policy option in the SQL Server Login window, you are basically telling the SQL Server to ...
John K. N.'s user avatar
  • 17.9k
13 votes
Accepted

Logon failed for login due to trigger execution

According to the screenshots you posted you are encountering an Error 17892 which translate roughly to a logon trigger error. You might have previously had a logon trigger that evaluated if you were ...
John K. N.'s user avatar
  • 17.9k
13 votes
Accepted

How to deny access to SQL Server to certain login over SSMS, but allow over .Net SqlClient Data Provider

You can use a server logon trigger to make custom logon validations and reject them whenever you see fit. You will see this trigger listed below "Server Objects" and inside "Triggers" if you are using ...
EzLo's user avatar
  • 3,306
13 votes
Accepted

SQL Server Logon Trigger Implications - are there any side effects possible in my scenario?

Database mail uses a Service Broker queue and an asynchronous background process to actually send emails, so performance shouldn't be a big deal. But logon triggers can easily cause downtime, so they ...
David Browne - Microsoft's user avatar
12 votes

SQL Server Error while creating User

The error message is stating that SQL Server cannot locate a login named Hiru, or you do not have the privilege required to create a user. The SQL Server security model consists1 of a server-level ...
Hannah Vernon's user avatar
  • 70.1k
10 votes
Accepted

NT SERVICE\SQLTELEMETRY User

This login is used for Extended Events. More information here: Extended Events logins I disabled it once, and I was no longer able to collect the data I needed. If I were you I'd keep the login as it ...
QWE's user avatar
  • 498
9 votes
Accepted

Creating a SQL Login and User on an AlwaysOn Replica

You need to create the login on the DB2 server. Use Robert's script so that it grabs the sid and password, well if it's a SQL account that is. Then grant the permission on DB1. That permission will ...
Tara Kizer's user avatar
  • 5,966
8 votes

Oracle 12c - Can not login to Enterprise Manager - XDB Login prompt

You should enable global port for EM Express to Manage a CDB and the PDBs, execute: exec dbms_xdb_config.SetGlobalPortEnabled(TRUE)
elkoo's user avatar
  • 181
8 votes

Disable multiple logins in one step

No, you can't feed a query to an ALTER LOGIN command. But you can build the command pretty easily using dynamic SQL: DECLARE @sql nvarchar(max) = N'', @cmd nvarchar(550) = N'ALTER LOGIN $$$ ...
Aaron Bertrand's user avatar
8 votes
Accepted

Why and how and where to use user without login

Imagine that you have somebody running an app, and this requires certain privileges. You do want to be able to log who is doing what and see who are logged in to your SQL Server. So each person need ...
Tibor Karaszi's user avatar
7 votes

How to deny access to SQL Server to certain login over SSMS, but allow over .Net SqlClient Data Provider

In the ideal sense, this is a process / policy / management issue. Even if someone knows the password, if it is against company policy for anyone but a DBA to connect to Production (well, you might ...
Solomon Rutzky's user avatar
7 votes
Accepted

What happens if Always On High Availability nodes have different connection default languages?

If the application is setting the language on the session when it logs in, then the default language doesn't matter. If it's not specifically setting the language on the session, then there is risk ...
Tony Hinkle's user avatar
  • 7,954
6 votes

Cannot log in using SA with SQL Authentication

Which Authentication Mode? Your server might have been configured to run in Windows Authentication mode only. This can be checked by opening up the Properties of your SQL Server instance in SQL ...
John K. N.'s user avatar
  • 17.9k
6 votes

Cannot login to SQL Server as a member of AD group

This is an old thread, but it was the first hit on Google when I encountered the same issue. My two cents: In my case it turned out that the AD group was a Distribution group, instead of a Security ...
Aaron Gonzalez's user avatar
6 votes

Logins Not being used in last one month

Since you've already added the successful login auditing to the instance, you can dump the error log data to a #temp table, then parse the individual login names, and then find all the rows in sys....
Aaron Bertrand's user avatar
6 votes

Granting SELECT permission on a object

I wanted to just run a SELECT statement against a table to see the value. Question Is there anything I can do at all? To find your permissions on the object you can use the function sys....
sepupic's user avatar
  • 11k
6 votes
Accepted

"sudo -u postgres psql postgres" does not ask for password

Postgres has a type of authentication called peer. If you look at pg_hba.conf you may see something like local all postgres peer or like local all ...
Francisco1844's user avatar
6 votes
Accepted

how to stop using sql server login credentials in a linked server?

I don't know if you got this resolved or not, but after recently going through our own (re)discovery of the requirements to get delegation running, I figured I could at least provide an answer in the ...
John Eisbrener's user avatar
5 votes

Login failed for user - Error 18456 - Severity 14, State 38

I had the same error with state code 38 caused by a typo of the name of the database in the connection string.
Rob Bowman's user avatar
5 votes
Accepted

ORA-01017: invalid username/password;logon denied

I have seen this only once so far, but there is an issue described in the My Oracle Suppport note: FIPS use causes ORA-01017: Invalid Username/password with Managed ODP.NET (Doc ID 1937500.1) I will ...
Balazs Papp's user avatar
  • 40.7k
5 votes
Accepted

Create a user with privileges to create users

To be able to create mysql users, you need to be able to insert into the users table in the mysql database. So create your master user and give him write access to the mysql database. The ...
til_b's user avatar
  • 587
5 votes

Create a user with privileges to create users

The privilege you are looking for is CREATE USER privilege. GRANT CREATE_USER on [db].[table] to <user_who_will_have_that_privilege>@<host> Example : GRANT SELECT, CREATE USER on *.* ...
bkaratatar's user avatar
5 votes

Cannot login to SQL Server as a member of AD group

TLDR; Make sure the user re-authenticates on the local windows machine that the client is establishing the connection from. From my experience, It seems SQL doesn't go check group membership when you ...
axawire's user avatar
  • 51
5 votes
Accepted

Where can I find a list of reserved logins?

If I try the following: create login [public] WITH PASSWORD = ''; I get this error message: Msg 15025, Level 16, State 1, Line 12 The server principal 'public' already exists. That makes me think ...
Joe Obbish's user avatar
  • 32.2k
5 votes
Accepted

Granting minimum select permission on system "sys" tables in every database

I can't seem to find a way around this other than creating a user for the login for every single database Starting with SQL Server 2014 the new CONNECT ANY DATABASE permission can be used to ...
sepupic's user avatar
  • 11k

Only top scored, non community-wiki answers of a minimum length are eligible