0

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?

Thanks

Improve this question
1
  • 3
    For the purpose of identification, you can specify the application name in the connection string instead of separate logins.
    – Dan Guzman
    Apr 14, 2020 at 11:52

1 Answer 1

Reset to default
0

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.

Since you asked for best practices, by using sysadmin like that you are breaking The Principle of Least Privilege which states that:

[...] Administrative tasks are broken out using fixed server roles, and the use of the sysadmin fixed server role is severely restricted.

  • I would like every SSRS, SSIS etc to use their own logins with only the required permissions.

I'd say creating and managing one login for each user, app, job or services would be going to the oposite extremity and that's a problem too, for, as your fellow DBA said, it would make it a really difficult job to manage so many logins, but you can achieve a middle term by using roles and Windows groups for that matter. Check the Principals doc.

  • 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.

Quoting @Dan Guzman's comment: For the purpose of identification, you can specify the application name in the connection string instead of separate logins.

Once you configured the Connection strings and application name you can retrieve it from sys.sysprocesses by using sp_who2, sp_whoisactive or sys.dm_exec_sessions DMV.

Improve this answer
4
  • 1
    Ultimately I'd like to see that sysadmin login removed. The Connection strings and application name link will help me a lot. I may just keep one login with db_datareader & db_datawriter. Thank you both for your help
    – beehive
    Apr 14, 2020 at 14:28
  • 1
    One point that wasn't made here--if you use just one login, if someone gets the password wrong when setting up a new application and locks out the login, this causes all applications using that login to fail. Unless you are changing passwords periodically, there is no management nightmare with one login per application. It takes just a minute to create a new login and store the credentials in an encrypted database.
    – Tony Hinkle
    Apr 14, 2020 at 14:42
  • @beehive, notice that keeping only one login for all applications is not safe for once that login has been compromised then databases related to those apps are compromised all at once.
    – Ronaldo
    Apr 14, 2020 at 14:46
  • @Tony Hinkle & Ronaldo, external application logins are not the issue here. This refers to in house ETL processes and reporting. I am unsure whether to give everything separate logins or not, but I will try to get the application name in the connection string implemented
    – beehive
    Apr 14, 2020 at 15:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.