December 28, 2015 at 12:04 pm
Trying to get to the bottom of a mystery.
We have an AD account registered to the server that is used a service account for a web application.
This account by itself does not have direct execute permission on all stored procedures, but somehow its inheriting this ability from something else.
The AD account is a member of an AD group that gives 'datareader' to this particular database. Would this be enough to execute the SP's that it does not have explicit 'execute' rights on? The SP's happen to be in its default schema too.
December 28, 2015 at 12:13 pm
The AD account is a member of an AD group that gives 'datareader' to this particular database. Would this be enough to execute the SP's that it does not have explicit 'execute' rights on?
Just focusing on this, no. Datareader does not allow you to execute a stored proc. Either it's getting the rights another way or the stored proc is not really running as that AD account. How is this account executing the stored proc?
-- Itzik Ben-Gan 2001
December 28, 2015 at 12:19 pm
Alan.B (12/28/2015)
The AD account is a member of an AD group that gives 'datareader' to this particular database. Would this be enough to execute the SP's that it does not have explicit 'execute' rights on?
Just focusing on this, no. Datareader does not allow you to execute a stored proc. Either it's getting the rights another way or the stored proc is not really running as that AD account. How is this account executing the stored proc?
Agreed - db_datareader does not confer execute permissions.
Is the AD account really being used for the connection or is it using a different account?
Is the AD account a member of any other AD groups that grant SQL Server permissions?
Does the account have any membership in any server roles?
Is the AD account granted permission to execute the specific stored procedure that's it's executing?
December 28, 2015 at 12:24 pm
Is the AD account really being used for the connection or is it using a different account?
Developer swears she changed it... but the behavior suggests otherwise
Is the AD account a member of any other AD groups that grant SQL Server permissions?
No, just the datareader.
Does the account have any membership in any server roles?
Is the AD account granted permission to execute the specific stored procedure that's it's executing?
No and no, and there are no application roles in play either.
I'm thinking the connection string is using some other credential, but the first rule on their problem chart seems to be 'blame the database, call the dba'.
December 28, 2015 at 12:34 pm
Manic Star (12/28/2015)
Is the AD account really being used for the connection or is it using a different account?
Developer swears she changed it... but the behavior suggests otherwise
Is the AD account a member of any other AD groups that grant SQL Server permissions?
No, just the datareader.
Does the account have any membership in any server roles?
Is the AD account granted permission to execute the specific stored procedure that's it's executing?
No and no, and there are no application roles in play either.
I'm thinking the connection string is using some other credential, but the first rule on their problem chart seems to be 'blame the database, call the dba'.
DBA = Default Blame Acceptor. 😉 I get it.
Ask to see the connection string in production for yourself. Don't have them email it or anything else - ask to see it.
If they won't show it to you, check it in test. See that the application works, disable the login they say they're using and see if the application still works. If it does, you have your answer that the application isn't using the login they say it is. Doing this in production depends on the application and users, as it's a bit more drastic.
December 28, 2015 at 12:36 pm
Ed Wagner (12/28/2015)
Manic Star (12/28/2015)
Is the AD account really being used for the connection or is it using a different account?
Developer swears she changed it... but the behavior suggests otherwise
Is the AD account a member of any other AD groups that grant SQL Server permissions?
No, just the datareader.
Does the account have any membership in any server roles?
Is the AD account granted permission to execute the specific stored procedure that's it's executing?
No and no, and there are no application roles in play either.
I'm thinking the connection string is using some other credential, but the first rule on their problem chart seems to be 'blame the database, call the dba'.
DBA = Default Blame Acceptor. 😉 I get it.
Ask to see the connection string in production for yourself. Don't have them email it or anything else - ask to see it.
If they won't show it to you, check it in test. See that the application works, disable the login they say they're using and see if the application still works. If it does, you have your answer that the application isn't using the login they say it is. Doing this in production depends on the application and users, as it's a bit more drastic.
i told her to open up a session in SSMS as this user and try to run that SP.
December 28, 2015 at 12:45 pm
Manic Star (12/28/2015)
Ed Wagner (12/28/2015)
Manic Star (12/28/2015)
Is the AD account really being used for the connection or is it using a different account?
Developer swears she changed it... but the behavior suggests otherwise
Is the AD account a member of any other AD groups that grant SQL Server permissions?
No, just the datareader.
Does the account have any membership in any server roles?
Is the AD account granted permission to execute the specific stored procedure that's it's executing?
No and no, and there are no application roles in play either.
I'm thinking the connection string is using some other credential, but the first rule on their problem chart seems to be 'blame the database, call the dba'.
DBA = Default Blame Acceptor. 😉 I get it.
Ask to see the connection string in production for yourself. Don't have them email it or anything else - ask to see it.
If they won't show it to you, check it in test. See that the application works, disable the login they say they're using and see if the application still works. If it does, you have your answer that the application isn't using the login they say it is. Doing this in production depends on the application and users, as it's a bit more drastic.
i told her to open up a session in SSMS as this user and try to run that SP.
If you have permission, you can check this yourself.
EXECUTE AS login = 'domain\user';
EXECUTE procedure_name;
REVERT;
Don't forget to execute that REVERT if the procedure execution fails or you'll stay connected as domain\user.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply