2

I'm running a procedure from database X to database Y.

On database Y there should be an insert from the user.

enter image description here

In this picture , the first column is the Login, the second one is the role that have all necessary permissions (it has the INSERT on the table we need).

The login is correctly assigned to the role, and the role have all necessary permissions.

but when I execute the procedure, I receive those famous errors:

Msg 229, Level 14, State 5, Procedure PROCEDURENAME, Line 165 [Batch Start Line 2]
The INSERT permission was denied on the object 'XXXXXXXXXXX', database 'DB', schema 'dbo'.
Msg 229, Level 14, State 5, Procedure PROCEDURENAME, Line 484 [Batch Start Line 2]
The INSERT permission was denied on the object 'YYYYYYYYYYYYYY', database 'DB', schema 'dbo'.
Msg 229, Level 14, State 5, Procedure PROCEDURENAME, Line 527 [Batch Start Line 2]
The INSERT permission was denied on the object 'YYYYYYYYYYYYYY', database 'DB', schema 'dbo'.
Msg 229, Level 14, State 5, Procedure PROCEDURENAME, Line 570 [Batch Start Line 2]
The INSERT permission was denied on the object 'YYYYYYYYYYYYYY', database 'DB', schema 'dbo'.

I created another test Login, gave the exact same permissions and it worked.

How can I find what is blocking the insert for this login? I even ran a trace to see if I can find more errors, nothing on eventviewer too.

The login has no explicit permissions. its just membership of the role with the permissions.

Role permissions are correct:

enter image description here

EDIT:

i tried everything since recreating the windows login and redo all permissions. What I noted is:

I could create a login (sql login with password) and it worked.

I tried as a test just insert data in the table with the original windows login, it failed, I then removed it from the ROLE and gave the INSERT permission to it and IT STILL FAILS. even with insert permission on the database, it fails to insert data.

10
  • 1
    Have you verified the user in database Y is mapped to the correct login?
    – Dan Guzman
    Sep 30 at 21:58
  • Yes I verified that. it's working normally and it's correct mapped. I will drop and recreate because we really couldnt understand whats going on.
    – Racer SQL
    Oct 1 at 13:14
  • @RacerSQL Are the logins (the real one and the test one) Windows or SQL logins? Are / were they both identical: same permissions at both server level and in both DBs? same membership in both server-level and DB-level roles (in both DBs)? If they are Windows logins, are they both members of all the same Windows / AD groups? Oct 1 at 14:25
  • Hey @SolomonRutzky. I just created an IDENTICAL login with IDENTICAL permissions (SQL user with password) and it worked. but It's not working with the login that is a windows login. I recreated the windows login, and it doesnt work. I just put it with the correct permissions (in the role that has insert permission) and even like this, it doesnt insert. I have no idea whats going on here. To test, I put the windows login as DB_OWNER of the database that it needs to insert, and it FAILS. jesus. Also there are no triggers affecting this problem
    – Racer SQL
    Oct 1 at 15:20
  • I edited the post with more information. even giving INSERT permission to the windows login, it doesnt insert data.
    – Racer SQL
    Oct 1 at 15:44
1

Testing with a SQL Server login isn't going to help if the problem is with a Windows login. This is due to Windows logins having the ability to authenticate via multiple sources (itself and/or one or more Windows groups).

How is the Windows login authenticating?

  • Directly (i.e. the login has an entry in sys.sys.server_principals),
  • via one or more Windows / AD groups (login does not have an entry in sys.server_principals, but is a member of one or more groups that do have an entry in sys.server_principals),
  • or both?

For Windows logins, all GRANTs and DENYs across all possible means of authentication are looked at. Even if there is an explicit login created within SQL Server for this Windows login, that won't cancel out, or take precedence over, any permissions associated with any Windows groups that they are a member of that are also listed as logins. It could be that there's membership in one or more Windows / AD groups that has a DENY or some other restriction, and those take precedence over GRANTs. List groups via NET USER {login} in command prompt.

O.P. replied:

Using EXEC xp_logininfo 'DOMAIN\LOGIN','all'; I could find that Permission path points to a group with DENY on it. !!!

This doesn't remove the problem, but it does identify the cause of the problem so that now it can hopefully be solved.

One possible solution would be to use Module Signing (link to my info site), though I would have to test to see how it would handle a DENY as those to take precedence. Either way, you wouldn't need to mess with the roles and direct permissions to objects, so probably still better even if it runs into the same problem caused by the DENY.

0

Use the ScriptLoginPermissions stored procedure, deploy it to any user database:

https://github.com/aleksey-vitsko/Database-Administrator-Tools/blob/master/Permissions%20-%20ScriptLoginPermissions.sql

Run it specifying login's name:

exec ScriptLoginPermissions 'domain\account.name'

Aside from showing all permissions for this account, this SP will also show group membership, including AD/Windows groups

Then you should check if any of the AD groups, have DENY permission on table in question
Run the SP specifying group name this time:

exec ScriptLoginPermissions 'domain\group.name'

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.