x

Database does not show whan adding ODBC connection.

I am running SQL 2005 on Win2003 server. Active Directory user group DB_All has full permissions on all databases on this server.

When adding an ODBC connection while logged as one of these users, after the first screen where you specify the default database, the database required is not one listed in the dropdown and when they try to connect they get an "Invalid Database error"

I have checked the permissions on these databases and cannot see why they all should not be listed when you click the dropdown.

There is only one database that does show, and I cannot see any difference in that one versus all the others.

Thanks.

more ▼

asked Oct 20, 2009 at 02:50 PM in Default

Larry gravatar image

Larry
21 2 2 2

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Did you restore the database from a different server? I am thinking the logins may be out of sync. Here is a script that I use to test for logins that are out of sync. The first query looks for the logins and the second query syncs all the logins.

--Query 1 to test for logins. DECLARE @Collation varchar(100) DECLARE @SQL VARCHAR(2000)

CREATE TABLE ##TempSync ( DB_NME Varchar(50), DBUserName varchar(50), SysLoginName varchar(50) )

SELECT @Collation = CONVERT(SYSNAME,DatabasePropertyEx('master','Collation'))

SET @SQL = 'USE [?] SELECT ''?'' DB_NME, A.name DBUserName, B.loginname SysLoginName FROM sysusers A JOIN master.dbo.syslogins B ON A.name Collate ' + @Collation + ' = B.Name JOIN master.dbo.sysdatabases C ON C.Name = ''?'' WHERE issqluser = 1 AND (A.sid IS NOT NULL AND A.sid <> 0x0) AND suser_sname(A.sid) IS NULL AND (C.status & 32) =0 --loading AND (C.status & 64) =0 --pre recovery AND (C.status & 128) =0 --recovering AND (C.status & 256) =0 --not recovered AND (C.status & 512) =0 --offline AND (C.status & 1024) =0 --read only ORDER BY A.name'

INSERT into ##TempSync EXEC sp_msforeachdb @SQL

SELECT * FROM ##TempSync

DROP TABLE ##TempSync

--Query 2 to Sync Logins

DECLARE @Collation VARCHAR (100) DECLARE @SQL VARCHAR(2000)

SELECT @Collation = CONVERT(SYSNAME,DatabasePropertyEx('master','Collation'))

SET @SQL = 'USE [?] DECLARE @DBUserName varchar(50) DECLARE @SysLoginName varchar(50) DECLARE SyncDBLogins CURSOR FOR SELECT A.name DBUserName, B.loginname SysLoginName FROM sysusers A JOIN master.dbo.syslogins B ON A.name Collate ' + @Collation + ' = B.Name JOIN master.dbo.sysdatabases C ON C.Name = ''?'' WHERE issqluser = 1 AND (A.sid IS NOT NULL AND A.sid <> 0x0) AND suser_sname(A.sid) IS NULL AND (C.status & 32) =0 --Loading AND (C.status & 64) =0 --pre recovery AND (C.status & 128) =0 --recovering AND (C.status & 256) =0 --not recovered AND (C.status & 512) =0 --offline AND (C.status & 1024) =0 --read only ORDER BY A.name

OPEN SyncDBLogins FETCH NEXT FROM SyncDBLogins INTO @DBUserName, @SysLoginName

WHILE @@FETCH_STATUS = 0 BEGIN EXEC sp_change_users_login ''update_one'', @DBUserName, @SysLoginName

FETCH NEXT FROM SyncDBLogins
INTO @DBUserName, @SysLoginName
END CLOSE SyncDBLogins DEALLOCATE SyncDBLogins ' EXEC sp_msforeachdb @SQL
more ▼

answered Oct 20, 2009 at 02:59 PM

Ken Simmons gravatar image

Ken Simmons
466 1 1 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x87
x37

asked: Oct 20, 2009 at 02:50 PM

Seen: 3375 times

Last Updated: Oct 20, 2009 at 02:50 PM