August 7, 2017 at 12:26 pm
I have three databases: trading, ref, rts. I created a new role and added a member to that role by doing the following:
1) Created a new role called db_executor
2) Created a new login ID
3) Created a new user ID in the trading, ref, rts databases.
4) Added the user ID as a member to the db_executor role in the trading, ref, rts databases.
In the trading database, I created a test stored procedure that selects columns from the ref database without any issues. However, when I try to run a similar stored procedure, on the trading database, that selects columns from a table in the rts database I get a 'Select Permission denied' from table name.
I set the database chaining to 'On' for all databases so I'm not sure why selecting a table in the rts database is giving an issue whereas selecting data from a table in the ref database works fine.
Here is some of the sql code that I ran:
use master
GO
create login [acli007] with password = 'passwordgoeshere', DEFAULT_DATABASE = [trading]
GO
use trading
GO
CREATE ROLE db_executor
GRANT EXECUTE TO [db_executor]
create user [acli007] from login [acli007]
GO
ALTER ROLE [db_executor] ADD MEMBER acli007
GO
I ran the same code on the ref and rts databases as I did on the trading database.
Any help would be appreciated.
Also, if the stored procedure inserted/updated/deleted data into a table in the ref or rts database, do I need to give it db_datawriter permissions? Or can it still run with the db_executor permission?
August 7, 2017 at 12:48 pm
Sue
August 7, 2017 at 12:52 pm
Thanks for your reply, Sue.
The only permission I gave db_executor is "GRANT EXECUTE TO [db_executor]"
Does it need more then that?
August 7, 2017 at 2:14 pm
rs80 - Monday, August 7, 2017 12:52 PMThanks for your reply, Sue.The only permission I gave db_executor is "GRANT EXECUTE TO [db_executor]"
Does it need more then that?
It should but permissions are also cumulative with deny taking precedence - the exception being a sysadmin as that will bypass security. There are no permission checks for sysadmins.
You could check the database permissions for that user (and the role) with something like:SELECT dp.class_desc,
dp.permission_name,
dp.state_desc,
OBJECT_NAME(major_id) as ObjectName,
grantee.name as Grantee,
grantor.name as Grantor
FROM sys.database_permissions dp
JOIN sys.database_principals grantee
ON dp.grantee_principal_id = grantee.principal_id
JOIN sys.database_principals grantor
ON dp.grantor_principal_id = grantor.principal_id
WHERE grantee.name like 'acli007' or grantee.name like 'db_executor'
ORDER BY grantee
Sue
August 7, 2017 at 2:17 pm
And I just realized, could also be in another role - you can use the example in books online to check database role membership:
sys.database_role_members
SELECT DP1.name AS DatabaseRoleName,
isnull (DP2.name, 'No members') AS DatabaseUserName
FROM sys.database_role_members AS DRM
RIGHT OUTER JOIN sys.database_principals AS DP1
ON DRM.role_principal_id = DP1.principal_id
LEFT OUTER JOIN sys.database_principals AS DP2
ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.type = 'R'
ORDER BY DP1.name;
Sue
August 7, 2017 at 2:28 pm
This is what I get back from running the query:
class_desc permission_name state_desc ObjectName Grantee Grantor
DATABASE EXECUTE GRANT NULL db_executor dbo
SCHEMA EXECUTE GRANT NULL db_executor dbo
DATABASE CONNECT GRANT NULL acli007 dbo
August 7, 2017 at 4:32 pm
Would help to see the stored procedure as well as who is creating the procedure and the permissions it is using.
August 7, 2017 at 6:41 pm
Sue
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply