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
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
ASKER
Where?
I'm using SQL Manager to try and create a user.
Is this something I set in the database properties?
I'm using SQL Manager to try and create a user.
Is this something I set in the database properties?
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
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
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
ASKER
Nevermind - got it!
I had an old ddl trigger in the database with the wrong SET commands.
I had an old ddl trigger in the database with the wrong SET commands.
Open in new window
at the top of your query