I have two environments/servers. Each has two databases, say DATABASE_A and DATABASE_B. There is a stored procedure in DATABASE_A (in both environments) that queries DATABASE_B and also calls multiple stored procedures on DATABASE_B. Most of those procedures are in the dbo schema, but one procedure is in another schema, say SCHEMA_X. That procedure reads from various tables in dbo and then truncates a table in SCHEMA_X before repopulating that table. That table and that procedure are the only two items in SCHEMA_X.
Now – all the above is applicable to both environments.
If I go to SSMS > relevant server > DATABASE_B > Programmability > Stored Procedures > SCHEMA_X.stored-proc-name > right-click > Properties > Permissions
Then nothing is listed. If I click to view schema permissions, again nothing is listed. This is again in both environments, but in one environment a client application presents an error saying it does not have permission to execute the stored proc. I tried brute force by granting the client's user group execute permissions on that schema. That seemed to take things further as the next error related to being unable to see the table, or not having permissions to access the table – which I could probably resolve by granting further permissions around insert/delete/update on that schema.
But my point is that initially both environments presented in the same way – at least in that spot that I checked (permissions against the stored procedure). So how should I debug this issue to determine why a client program in one environment is able to execute the stored proc (and effect the truncate/insert into the table) while the other reports it does not have permission? Is there no single comprehensive query that lists for you all permissions within a database?
Best Answer
I would start by checking whether cross database ownership chaining is involved You should be aware that this can introduce serious security problems
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/enabling-cross-database-access-in-sql-server
I have had success with the scripts provided by Louis Davidson for working out effective permissions but I don´t know if his ambitions extend to cross database permissions
link for reference
https://www.red-gate.com/simple-talk/blogs/calculating-a-security-principals-complete-effective-rights/
Have you runa trace or extended events session to see the specific place you get the permissions error ?