0

There is a specific user who is the job's owner in SQL Server. I want to limit his access to the database.

I searched the web and find the script fit my situation at SQL Server: can you limit access to only one table. I test the script to deny access to all tables, so the job should fail if the user cannot access the tables. However the job still runs without problem.

These are the steps I did to create the user and deny access to all tables:

  1. Created the user and its schema owner is dbo in master
  2. Create the user in the database
  3. Run the below script to deny access to the table for the user

There is my code:

 USE [dtTest]
 GO

 EXEC sp_msforeachtable "DENY SELECT ON ? TO [tester];"
 GO

Would someone tell me what I should do? Thanks in advance

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Y Chan
  • 317
  • 2
  • 18
  • jobs get executed under the service account of your SQL Server Agent, not the job owner. This also means that your question does not apply to your intent and your conditions. – Ricardo C Aug 13 '21 at 22:47
  • You will have to setup an Agent proxy for it to run under that user’s account. – RBarryYoung Aug 14 '21 at 00:15

0 Answers0