Search code examples
sqlsql-server-2012odbcms-access-2007database-administration

SQL Server login error over ODBC connection

I'm working on a project using SQL Server 2012 and I am running into an issue while attempting to update a login.

I was attempting to add DDL permissions to a login and when I went to hit OK and close the properties window, I received this error.

SQL Server Error

I closed the login properties window and reopened it to find that the login's database role changes had indeed been updated to reflect the changes I made.

It looked to me like my changes had been committed, and then this error popped up.

I went to connect over an ODBC connection I had made earlier for the same login, but discovered I no longer could, I was getting this error from the ODBC.

ODBC Error

I know for a fact that this ODBC connection was working for this login at some point before my changes since I had a linked table that was using this same connection. It was able to both pull down updates from the SQL Server and push through changes back up to the server level.

Can anyone help shed some light onto what a solution to these errors may be? I assumed they were related since they both began occurring this morning, but I'm not positive of that fact. My database administrator is out of the office till tomorrow, so I'd really like to get this resolved before then.

This is what I get when I try to use that logon through SSMS

SSMS logon

Solution

  • I'm putting this into an answer, since it solved OP's problem. I don't know what happened during/after the initial error (when granting DDL permission), nor exactly what the other errors were pointing to.

    Because the user was unable to login through ODBC or SSMS, the first step would be to check the user and login properties in SSMS, to ensure that, for example, it is enabled, and that it was granted permission to the database.

    In the comments, OP found that the user was not setup with GRANT.