February 3, 2014 at 10:18 am
I've created the following script to report on the status on SQL logins:
EXEC msdb.dbo.sp_send_dbmail
@recipients= N'XXXX',
@subject = 'ED - SQL_Logins_Report',
@profile_name = 'XXXX',
@query =
'SELECT @@SERVERNAME
SELECT is_disabled, name FROM sys.server_principals
WHERE type_desc=''SQL_LOGIN''
ORDER BY 1'
When run manually from a query window, the script successfully reports a listing of all SQL logins (6 in total) and their respective disabled state; but once configured as a SQL job, it runs successfully but only reports the disabled state of the 'sa' login.
Any reason why running a simple command as above as a SQL job would report different results and could I do to make this work?
February 3, 2014 at 10:44 am
Look at the job history.
Check what user executed the job step (likely whatever user that is running the SQL Agent Service)
make sure that user has the appropriate permissions.
February 4, 2014 at 7:19 am
Hi arnipetursson,
You are correct, the SQL job is executed under the same account under which the SQL Server Agent service runs. As this wasn't originally granted any specific rights within the SQL instance, I've now created a SQL login for this account and granted it sysadmin role but still, the query does not report all the reports I expect to see if I run the same query manually.
I appreciate granting the SQL login associate with domain account sysadmin role probably isn't a clever idea, but I'm at a loss as to what else do
Any other suggestions?
Thanks,
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply