| Author |
Topic |
|
aliciap1214
Starting Member
10 Posts |
 Posted - 2010-06-28 : 12:41:01
|
| I came into work this morning to find I could no longer log into one of my SQL Server 2005 databases via ODBC. Upon checking the User Mapping page, I found that our master login that uses Windows Authentication was no longer mapped; so I re-mapped it. And when I went back into the User Mapping page, the "Map" box was still unchecked. I then tried a couple more times, and for some reason SQL Server is not allowing this id to map. Have any of you experienced something like before, and if so how did you work around it? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
aliciap1214
Starting Member
10 Posts |
Posted - 2010-06-28 : 16:10:14
|
| I'm fairly new to SQL Server. Do you know of an article or something that I could reference that explains how to do so? |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
aliciap1214
Starting Member
10 Posts |
Posted - 2010-06-29 : 14:23:39
|
| I checked to see if the User is orphaned, and it is not. The SID ID appears to be fine. The only thing I did notice is that the Default_Schema is set to NULL. I did read, however, that if it is not set, it defaults to dbo. So that may or may not be the problem. I would ALTER the user to set the Default_Schema, but I have six other DBs that are working fine with this user, and don't want to do anything to mess them up. Any other ideas? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
aliciap1214
Starting Member
10 Posts |
Posted - 2010-06-29 : 14:46:04
|
| Sure. In SQL Server Management Studio under Security/Logins I have a WINDOWS_GROUP login called "WEST-CA191", and when I right click on it and select "Properties", and then "User Mapping" there is a list of Databases I can map to by checking the "Map" checkbox. Unfortunately, since Monday I am no longer able to Map to one of my databases: "EtsCubeData", but 6 other databases have no issue with this login. Each time I re-check it, it defaults back to "unchecked". That's my problem. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
aliciap1214
Starting Member
10 Posts |
Posted - 2010-06-29 : 15:43:43
|
| Yes, I am. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
aliciap1214
Starting Member
10 Posts |
Posted - 2010-06-30 : 09:31:22
|
| Yes, it exists in the "users" list. And I, unfortunately, can't delete it without hosing up 6 other databases. These databases all use this WINDOWS_GROUP (which everyone in our company is part of), and deleting it would mean having to re-add it to all our stored procedures, etc.; something I was trying to avoid. So there isn't any other solution other than doing this very drastic thing? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
aliciap1214
Starting Member
10 Posts |
Posted - 2010-06-30 : 10:49:26
|
| Yes, it exists as both a login (at the server level) and a user (at the database level). And I could delete the user at the database level, but the problem will still exist at the Server level (which keeps me from connecting via ODBC. Unfortunately, we're using MS Access as a front-end, and this is the only way I can link tables, and do recordset connections).I did try creating a separate login with a password, and a new ODBC connection, and that isn't even working. I'm beginning to think there is something wrong with the database itself. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
aliciap1214
Starting Member
10 Posts |
Posted - 2010-06-30 : 10:59:02
|
| Actually, Tara, you did help me. I just figured out the problem. I went into the database's properties, and looked at the WINDOWS_GROUP permissions, and they were all unchecked. I selected all the necessary permissions, and it is back up and running. I have no idea why I didn't decide to do that sooner (would have saved me hours of headache), but at least I finally figured it out. Thank you for your patience. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
aliciap1214
Starting Member
10 Posts |
Posted - 2010-06-30 : 11:04:36
|
| Hey, I do have one more question, if you don't mind. My users need to be able to create a table and then delete it (for instance a table called "tmpFilesToImport" that also includes the date and time: "tmpFilesToImport_100630_125322"). Can I allow this without giving them ALTER rights? I tried using table variables for this, but had trouble accessing them outside of the current routine. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
aliciap1214
Starting Member
10 Posts |
Posted - 2010-06-30 : 11:43:12
|
| Yeah, I was aware of that. Okay, well thank you for your help. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
User Mapping Fails
Tara Kizer