Apologies for asking an opinion type question, but I'd like to know what is best practice.
I have a bunch of sql servers and every job as well as every connection manager for every SSRS report & SSIS project all use one login, sysadmin of course. I see it as a problem for two reasons:
- I would like every SSRS, SSIS etc to use their own logins with only the required permissions.
- I also would like to know immediately what's causing blocking etc., rather than seeing one sysadmin login every time and then checking to see what's running the offending query.
My fellow DBA believes this is madness due to the number of logins required (hundreds per server), suggesting one login per database with the db_datareader role would be more appropriate. Can anyone share links to articles or offer advice?