SA user unable to access a database

Question

SA user unable to access a database

Arsh

SSCertifiable

Points: 6345
More actions
July 25, 2018 at 12:20 pm

#354944

Dear All,
I have scenario where in I have to create a user who has no access to a database D2 and has READ/Write on D1. D1 and D2 both are on same SQL server instance.
This user would login to D1 and invoke an SP on D2 .
To make this happen , I added the user in D2 and Granted execute on the SP.
Also , it assigned role membership db_denydatareader , db_denydatawrite .
But since the user has to still be able to execute the SP , I modified the SP to run using 'WITH EXECUTE AS OWNER' . The owner of the database D1 is SA.When I run the Exec from D1 with this user , I get an error saying
'The server principal UserName is not able to access the database D1 under the current security context'
Its strange that the user SA is a super user and owner of D1 but still unable to perform Insert/Deletes . I created Certificates on both the databases but still it doesn't work .
This is not working for this of databases only but the same thing works well for another set sometime back on the same machine , same SQL server instance.
Would be great if anybody can help me in this . Quick suggestion would be even better as its a very time constrained task.
Please bear with my Typo .
Thank you in advance.
...Arshad

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90244 More actions · Answer 1

You don't need to deny privileges to the user in D2 - which is being done by adding the user to the deny roles. Once you grant execute permissions on the stored procedure - that user should be able to execute the procedure with no other privileges.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Arsh SSCertifiable Points: 6345 More actions · Answer 2

Jeffrey Williams 3188 - Wednesday, July 25, 2018 12:44 PM
You don't need to deny privileges to the user in D2 - which is being done by adding the user to the deny roles. Once you grant execute permissions on the stored procedure - that user should be able to execute the procedure with no other privileges.

Hi Jeffry,
Thanks for the reply. The user in D2 should not be able to Read anything from D1 yet should be able to call this SP , that's why the user is assigned the db_denydatareader , db_denydatawrite role memberships. And that's why I modified the SP to run with 'WITH EXECUTE AS OWNER' so that it gets the access to the tables in the context of running the SP. This worked well for database set D3/D4 on the same server but doesn't work for database set D1/D2.

Thanks....Arshad

Jeffrey Williams SSC Guru Points: 90244 More actions · Answer 3

You do not need to add users to the db_denydatawriter and db_denydatareader roles. If you grant the user execute access to the stored procedure then that is the only permission they will have in that database unless they are granted other permissions through membership in another role or security group.

Adding the user to the deny roles is not necessary since the user does not have read/write access anyways.

Remove the user from those roles and remove the EXECUTE AS OWNER from the stored procedure - neither is necessary. Then test the user's access using:

EXECUTE AS login = 'user login';
GO

EXECUTE {database}.{schema}.procedure
GO

REVERT;
GO

If the user has been granted execute on the stored procedure - they can execute that procedure without any additional permissions. If they are not able to access the database then they have not been granted access to the database or are being denied access to that database through some other membership.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Arsh SSCertifiable Points: 6345 More actions · Answer 4

Jeffrey Williams 3188 - Thursday, July 26, 2018 11:58 AM
EXECUTE AS login = 'user login';
GO
EXECUTE {database}.{schema}.procedure
GO
REVERT;
GO

Hi Jeffrey,
Got it working by removing the certificates and re-created afresh. This time I tried to associate the user with the certificate where it failed saying the use already exists(which is right). After this failed command of creating user from certificate , the SP could be called from the other database without issues. Not sure how its working after a fortnight. Please note that I provided 'EXECUTE AS OWNER' so that the user has no issue accessing any objects accessed by the SP. Thank you for the inputs from your side.

Thanks....Arshad