Cannot open database but connection is ok

Question

Cannot open database but connection is ok

halifaxdal

SSCoach

Points: 19741
More actions
January 14, 2014 at 10:32 am

#281101

Hello,
I have a new Dundas Dashboard project that requires a data connection to a server/db.
There was a working domain id but it is decided that another id must be used.
To me, this sounds a simple work, just configure the new id as exactly the same privilege as the old and working id.
However, the new id just couldn't get working.
In Dundas Connection to the server, test connection works; but to further discover the data structure, it failed with error: cannot open database xxxxx
It was said the new id has exactly the same privilege like the old one.
But I just can't buy this, there got to be some discrepancies between the two IDs.
But what could they be?
Can anyone give any hint that I can request the DBA to check?
Thank you very much. Any clue is appreciated.

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

halifaxdal SSCoach Points: 19741 More actions · Answer 1

By the way: we have to apply least privilege to the id, it would be troublesome to give more privilege to the id, currently it is data reader, and like I said, it was said it has same configuration as the old working ID on the subject DB and server but I doubt it, I believe there is something missing.

Thanks.

HanShi SSC-Dedicated Points: 33519 More actions · Answer 2

Check if the database user is connected to the login of the new login ID. Open a query windows in SSMS, set the focus to the desired database and execute the command: sp_change_users_login

This command will return all users (database-level) that are not connected to a login (instance-level). See http://technet.microsoft.com/en-us/library/ms174378.aspx for more information.

Btw: is the default database for the login set to the correct database?

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

halifaxdal SSCoach Points: 19741 More actions · Answer 3

Thanks for reply.

I do not have both the IDs, I will only use the new one and it's domain ID, meaning I don't have password for it.

HanShi SSC-Dedicated Points: 33519 More actions · Answer 4

You need a login with enough permissions (for example the login you used when you created the new ID) to execute the "sp_change_users_login" command. This command will give information about every user within the database(if they are not connected to a associated login).

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

halifaxdal SSCoach Points: 19741 More actions · Answer 5

I still don't get the point here.

I had a colleague with privilege and ran the sp_change_users_login, apparently some parameters must apply.

I had him ran sp_helplogin id and both IDs return same result.

I also had him check the default db for the two IDs, it's master.

Thank you.

HanShi SSC-Dedicated Points: 33519 More actions · Answer 6

halifaxdal (1/14/2014)
I had a colleague with privilege and ran the sp_change_users_login, apparently some parameters must apply.

Sorry if I wasn't clear in my previous post. The full command is:

exec sp_change_users_login 'report'

Some background information:

In SQL Server there are two levels of access. The first is a LOGIN and defines the access on instance-level. Next there the USER and this defines the access to a specific database. The LOGIN and the USER are connected by a SID. If you delete a LOGIN and recreate it with the same name through the GUI a new SID is assigned to the LOGIN. Therefor the new LOGIN is not connected to the existing USER in the database(s). You can access the instance using the LOGIN, but you won't have access to the database. The stored procedure "sp_change_users_login" can be used to (re)connect the USER to the LOGIN.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

HanShi SSC-Dedicated Points: 33519 More actions · Answer 7

Maybe as a more drastic solution:

- delete the USER from the database(s)

- delete the LOGIN from the instance

- create a new LOGIN and assign access rights to the database(s) (when using the GUI it will create the associated USER at the database(s) )

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

halifaxdal SSCoach Points: 19741 More actions · Answer 8

Thank you very much for your advise, I will have to talk to my colleague tomorrow as he already left.

I guess delete/re-add would work.

halifaxdal SSCoach Points: 19741 More actions · Answer 9

The issue was resolved: the two IDs were not identical!!!

The new ID doesn't have the VIEW ANY DATABASE privilege which the DBA said By default, the VIEW ANY DATABASE permission is granted to the public role.

Now I have new question but I will start a new thread.

Thanks.