November 27, 2006 at 2:28 am
hi,
i am using Microsoft SQL server 2000 and .Net 2003. my machine OS is Windows NT 2000 professional. i got a database which was used in different machine and restored it in my machine. I tried to run a stored procedure from that restored database through .Net application but i get error saying "User does not have permission to perform this action on this table <tablename>". the current login is a part of administrators group. I added the current login in SQL enterprise manager and gave access to this database. still i get this error. i would like to know how to resolve this.
thanks in advance,
Pradeep
November 27, 2006 at 2:37 am
Can you log on as the user on the database using enterprisemanager, queryanalyzer? The securityid of the user in the database might be different than the one in the master-database
November 27, 2006 at 3:07 am
You are using Windows integrated authentication by the sounds of it.. Correct?
Could you run Query Analyser, execute the following script, and post its output?
use [MY_DB_NAME] --replace appropriately GO sp_change_users_login 'report' GO
It may be that you need to remap the users. You can do this by executing
sp_change_users_login 'auto_fix', LOGIN_NAME --replace appropriately
I've also seen people have trouble when they are attempting to pass a windows account's username + password in the SQL connection string. This will not work, ever. If you want to pass a username & password then you'll need to switch your server into Mixed authentication mode (Windows + SQL authentication) and then create a SQL server login with the appropriate name + password. Then you'll need to grant this login access to the database as a database user.
November 28, 2006 at 7:21 am
Have seen this myself when the user's default database has been deleted/renamed. Change the default to Master - give it another go.
Cheers
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply