Avatar of JRuiter
JRuiter
 asked on

Error when trying to create SQL Server login: SELECT failed because the following SET options have incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER'. Verify that SET options are correct for use wit

Greetings,

When I try to create a login on my SQL Server 2005 SP3 installation, I get the following.

SELECT failed because the following SET options have incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods. (Microsoft SQL Server, Error: 1934)

I've been unable to find any information regarding this error in relation to creating SQL logins.  Any help appreciated.

Thanks
Jason
Microsoft SQL Server

Avatar of undefined
Last Comment
JRuiter

8/22/2022 - Mon
barlet

try to set

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON

Open in new window


at the top of your query
JRuiter

ASKER
Where?

I'm using SQL Manager to try and create a user.

Is this something I set in the database properties?
barlet

when you create a user at the top of the windows there should be a button named "Script" click to it and then set the parameters from the previous post.

at the end you should see something like:



USE [master]
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON

GO
CREATE LOGIN [testUser] FROM WINDOWS WITH DEFAULT_DATABASE=[test]
GO
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
JRuiter

ASKER
Above command gave me following:

Msg 1934, Level 16, State 1, Procedure ddl_audit_login, Line 20
SELECT failed because the following SET options have incorrect settings: 'ANSI_NULLS, QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or query notifications and/or xml data type methods.

Any idea why this happens in the first place?  My production database doesn't do this.

ASKER CERTIFIED SOLUTION
barlet

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
JRuiter

ASKER
The other one was OK - I'm trying to add an domain user anyway.

Is there a reason why this happens in this database and not my other one?  The server in question is SQL 2005 Developer.  I restored a database from a SQL 2005 production instance.  Since the ERP application that runs against it also need to create users, I'd like to find the root cause....

Thanks
JRuiter

ASKER
Nevermind - got it!

I had an old ddl trigger in the database with the wrong SET commands.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.