Tuesday, 29 November 2016

Common SQL Agent Misunderstanding

Given this is a common misunderstanding lets look at why.
First when a SQL Job runs as a person that is not part of the SysAdmin you will see executed as and then the user in our case that LAB\Joker, if on the other hand it was run as SysAdmin you would see the name of the account SQL Engine is running under.

So let's stay with our LAB\Joker for the moment, what does it mean when we say executed as.
What it means is that the T-SQL is run with the same permissions as that user as in SQL, so if he has database owner rights then it will have database owner rights.

However once the job steps outside of the SQL server, for example grabbing a file from a network share, you are no longer inside SQL.


So if you are outside of SQL when getting the file from the network share what account is SQL using to get the file ?  The answer is simple it's using the SQL Agent account.

Now if you SQL Agent service is running under server\localsystem it means unless the server$ is part of the share it will not have access or you could add everyone to having permission but I do not recommend using the everyone on any share ever.

Since computer accounts are hidden in windows you will need to add the $ sign or it will not show up.

The better option is to create a domain service account for the SQL agent to run under and grant that accounts access to the share.

Out example job had the following dummy data
10,Field2,Field3,Field4
15,Field2,Field3,Field4
46,Field2,Field3,Field4
58,Field2,Field3,Field4

And use the follow T-SQL statement to do your own import,
--Create table
CREATE TABLE myimporttable (
   Col1 smallint,
   Col2 nvarchar(50),
   Col3 nvarchar(50),
   Col4 nvarchar(50)
   );
GO
--Begin import
BULK INSERT myimporttable 
  FROM '\\dom\Share1\import.txt' --Over Network
  WITH (
  FIELDTERMINATOR = ',',  --CSV field delimiter
   ROWTERMINATOR = '\n',   --Use to shift the control to next row
   TABLOCK
)
GO
--Check content
SELECT * FROM myimporttable ;
GO
--Clean Up
Drop table myimporttable 

I recommend trying the permission a few times till its clear in your head as this can be complex subject as the permissions are layered.

If this was all too much theory you can also follow along on youtube where I have a practical example.