SQL Server Performance Forum – Threads Archive
change login account or role in Query Analyzer
Hi, all: I am wondering if there is any procedure or utility to allow me to execute query as a different user or with a different role in Query Analyzer. Here is the problem I ran into, maybe someone could suggest another solution if it’s not possible to impersonate another user in Query Analyzer. We use Windows Authentication for the SQL server 2000. I have developed an access adp application and users access/insert/delete/update records through the adp application only. Users are assigned datareader and datawriter roles. The problem is they cannot update a particular table but they can insert and delete records in that table through adp application and they can do update/insert/delete on all other tables that we tried. I checked the permission on that table and it doesn’t look any different. I have changed users role to ddladmin and it still didn’t work. I don’t think it’s related to the application role because when I tried through the adp application front-end, everything works for me ( I have sysadmin role). I would like to figure out if the problem is on the SQL Server side or the Access side, by execute the update query through query analyzer directly as the user, but I couldn’t find anything to let me impersonate the users. Any help would be appreciated. Thanks. JingHi,
have you check permission with you SQL User or Windows User ?! have you assign some explicit permission using GRANT / REVOKE command ?! Hemantgiri S. Goswami
[email protected]
"Humans don’t have Caliber to PASS TIME , Time it self Pass or Fail Humans" – by Hemantgiri S. Goswami
Hi, Can you get the update query from adp application. Just open ur adp application in development environment, then put a break point and get the sql(Update statement) and run in query analyzer. Check your update field and other where criteria(Wherever necessary). In some cases Nulls will play excellently. Just keep an eye on null data.
In QA, use SETUSER ‘Jing’ to change to a different login, and SETUSER ” to return to the original login. The connected login must be a member of sysadmin or db_owner, otherwise SETUSER will fail, and the instructions after SETUSER will be executed under the connected login’s own permissions.
By the way, issues with unclear permissions could be caused … (1)
The table does not have a primary key. This is a problem for ODBC linked tables in Access – could be different in an ADP, but worth investigating anyway. (2)
The login belongs to multiple database roles, and one of those roles is explicitly denied the permission: a big red cross on the list of permissions. Permissions that are explicitly denied overrule any corresponding permission granted under another role to which the same login belongs. Permissions that are not granted (not a green check mark, nor a red cross on the list) do not overrule, so you could REVOKE the denied permissions, or you could reconsider the roles to which the login belongs.
Thank you! SETUSER works! I was able to impersonate the user and found out the problem did come from SQL Server end. That table was created by a previous employee and I wasn’t aware of a trigger that add records to another table. Thanks for everyone’s help here. Jing
]]>