jana
asked on
SQL VB connection works in one PC and doesn't in another
We have an application running within a windows server, with the following vb6 code to connect to it's sql 2008 instance:
The problem,
If the user is administrator, all works great, if the user is non-admin, doesn't connect and displays "Error number: -214721783".
We went into sql to the problem table and granted permission to select/delete/insert/updat e and still same error. Note that the non-admin user has access to all other tables with the database, it's only with this table; thus, sql permission of some sort.
Please advice.
cnn.Open "Provider=SQLOLEDB; " & _
"Initial Catalog=" & dbname & "; " & _
"Data Source=" & sqlserver & "; " & _
"integrated security=SSPI; persist security info=True;"
(Which makes the sql connection 'Provider=SQLOLEDB; Initial Catalog=DBNAME; Data Source=SQLINSTANCE; integrated security=SSPI; persist security info=True;')The problem,
If the user is administrator, all works great, if the user is non-admin, doesn't connect and displays "Error number: -214721783".
We went into sql to the problem table and granted permission to select/delete/insert/updat
Please advice.
Can this user connect to SQL Server using SSMS and run SELECT command ?
ASKER
Just checked and logged into the server with the non-admin user name, ran SSMS, and no; cannot even log in.
Thats the issue. Please fix it !!
Give him the proper access :)
Give him the proper access :)
ASKER
Please provide the steps.
Thank you.
Thank you.
ASKER
FYY:
We went into Sql 2008 Instance >> security >> login and check the following:
- there is a password for the user in General,
- in server role he is 'public',
- in User Mapping he has access to the correct databases,
- in Securables is empty,
- in Status, permission granted and login enabled
What are we missing?
We went into Sql 2008 Instance >> security >> login and check the following:
- there is a password for the user in General,
- in server role he is 'public',
- in User Mapping he has access to the correct databases,
- in Securables is empty,
- in Status, permission granted and login enabled
What are we missing?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Done. Same error as stated in the question. Also tried to get the user logged in and can't.
Pls post the error?
ASKER
Error number: -214721783
Login failed for user SqlInstanceName\UserName
Login failed for user SqlInstanceName\UserName
ASKER
FYI,
Please note that user has access to all other tables within the database, it's only with a specific table. The user as all users has been working with the database for more than 5 years without giving them owner rights to the database.
The problem is with this specific table that is used thru a vb6 apps we have. Again, all apps the users uses to access and work with the database, where the table was created recently, has no problem.
Since the problem is with this table only, what can de causing this problem?
Please note that user has access to all other tables within the database, it's only with a specific table. The user as all users has been working with the database for more than 5 years without giving them owner rights to the database.
The problem is with this specific table that is used thru a vb6 apps we have. Again, all apps the users uses to access and work with the database, where the table was created recently, has no problem.
Since the problem is with this table only, what can de causing this problem?
Okay , So what they are storing in that table. Sensitive info. ?
ASKER
It's a new table storing customer specific info; not that sensitive data.
ASKER
We have gone over and over this problem. We have looked into what we think is everything and still searching; the only conclusion that we can come up with is window-user-permissions on table.
The curios thing,
So conclusion,
Hope this info helps, help us.
UPDATE:
We created a new data base and a new table, pointed the apps there and same problem; a non-admin running the apps displays the error:
Hence, it's something in the SQL instance where a non-admin user cannot access new DB or Tables.
The curios thing,
- When Windows administrator logs into Windows Server and logs in the application of said database, he has no problem with the new table created (a vb6 apps that uses it).
- If the Admin logs into Windows Server and then logs to the application with a non-admin username (an SQL username that the apps uses), again, he has no problem with the our vb6 apps.
- However, if the non-admin logs into Windows Server and logs in the application of said database, they have no problem, but as soon as they run the vb6 apps they get Error number: -214721783 'Login failed for user SqlInstanceName\UserName'
- Finally, if the non-admin user logs into Windows Server and then logs in the application of said database with "SA" (SQL user), and run the vb6 apps they get the Error
So conclusion,
- The problem has to be the permissions Windows User created in the Windows Server.
- Why?
Because the same vb6 apps that accesses with the new table functions excellent when the user Administrator logs in the server and it same exact apps doesn't work when a non-administrator logs into the server.
Hope this info helps, help us.
UPDATE:
We created a new data base and a new table, pointed the apps there and same problem; a non-admin running the apps displays the error:
Error number: -214721783
Login failed for user SqlInstanceName\UserName
Login failed for user SqlInstanceName\UserName
Hence, it's something in the SQL instance where a non-admin user cannot access new DB or Tables.
ASKER
Besides In User Mapping --> Database role member >> db_owner, we also granted permission.
Thanx! Your entry gave us the lead!
Thanx! Your entry gave us the lead!