Timeline for Client has Granted Update/Insert/Delete - may accomplish this with Stored Proc, but not with a direct query
Current License: CC BY-SA 4.0
10 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Mar 11, 2022 at 14:42 | comment | added | David Browne - Microsoft |
Did you test with execute as user='Joe'? And it's much easier to understand if you include the script for the commands you run in in your question instead of comments.
|
|
| Mar 11, 2022 at 13:20 | comment | added | Laura | So next I ran GRANT Delete to Joe; GRANT INSERT to Joe; GRANT UPDATE to Joe. This ran successfully, We ran SELECT * FROM fn_my_permissions(null, 'database'), and these three permissions are still missing, and he still cannot run an update on the table in the database. I am not a dba, but have worked with the architecture side for years, and can feel my way around the admin side. I have never seen this before and would like some insight into what hidden DENY or conflict of logins or permissions could be causing this. | |
| Mar 11, 2022 at 13:19 | comment | added | Laura | I first looked at the properties under his LOGIN. In User Mapping, he is mapped to database A as owner. Then I looked under database A Users. Under Membership he shows up db_owner. Under Securables and Schemas nothing is marked. We ran SELECT * FROM fn_my_permissions(null, 'database'). The result was missing "DELETE, UPDATE, and INSERT". | |
| Mar 11, 2022 at 13:19 | comment | added | Laura | User wants to update a table B in database A. He tried to run: Update A.B SET column1 = '0'. This fails with the message user does not have update permissions. Next user creates a stored procedure with the same update query. It runs successfully. Why is this happening? | |
| Mar 10, 2022 at 21:53 | comment | added | David Browne - Microsoft | Add additional details to your question in the form of the queries you have run and the results returned that outline the situation. Eg "mapped as owners" of what? What are the databases, schemas, and users involved. If possible create an isolated repro of the issue. | |
| Mar 10, 2022 at 21:48 | comment | added | Laura | Thank you for the additional ways to check.The problem is that I can see permissions to UPDATE/INSERT/DELETE are missing, but Granting them does not add them, and they are mapped as owners, as well. The Grant statement ran successfully according to the message, but it didn't add the permissions as it should. What could be causing this? | |
| Mar 10, 2022 at 0:35 | history | edited | David Browne - Microsoft | CC BY-SA 4.0 |
deleted 12 characters in body
|
| Mar 10, 2022 at 0:26 | history | edited | David Browne - Microsoft | CC BY-SA 4.0 |
added 749 characters in body
|
| Mar 9, 2022 at 22:22 | history | edited | David Browne - Microsoft | CC BY-SA 4.0 |
added 709 characters in body
|
| Mar 9, 2022 at 22:16 | history | answered | David Browne - Microsoft | CC BY-SA 4.0 |