How I should map the "work" of SQL LOGIN? RRS feed

  • Question

  • Hello, 
    I would like to ask you for advice.

    I have SQL LOGIN in the instance of SQL Server, which 
    has rights at the SQL Server level: 
    securityadmin, setupadmin and sysadmin.

    I don't know what "work" this login does, but 
    I want to at least kick it out of the sysadmin role 
    (previously, everyone was given sysadmin and 
    nobody dealt with the rights much).

    Can you please advise me how I should map the "work" he does?

    E.g. create an 
    Extended Events Session that will catch some events (which?) 
    for the selected SQL LOGIN and 
    let it run, at least one work cycle (eg 24 hours) or otherwise?

    The goal is not to limit it, but to avoid sysadmin, 
    which it certainly does not need.

    Thank you in advance for your reply.

    Lada vyvojar

    Monday, June 22, 2020 8:48 AM


All replies

  • you should require control server permission.XErights 1


    Monday, June 22, 2020 9:26 AM
  • I would kick of that login from all roles and see what complain do you get and so operate accordingly 

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, June 22, 2020 11:53 AM
  • Hi, Uri Dimant, 

    That's a very fun answer!

    It's on the production server.

    Lada vyvojar

    Monday, June 22, 2020 12:20 PM
  • It is extremely difficult to track everything a login does.  In addition, you would have to track it for a long period of time to discover everything.  This login might only be used once a quarter.

    As Uri said, the simplest thing is to disable the login (or remove it from sysadmin) and wait for someone to complain, even on a production server.

    Monday, June 22, 2020 12:35 PM
  • Hi Lada,

    Also check following posts for reference:
    Track A SQL Server Login Activity
    Enable Audit log To track Particular SQL Login Activities

    Best Regards,

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Lada vyvojar Tuesday, June 23, 2020 10:38 AM
    Tuesday, June 23, 2020 2:16 AM
  • Although you can do this, keep in mind, you may need to monitor the login for a long period of time.  This login might be used for a specific purpose once a quarter, or only intermittently, or the person using it might be on vacation, or only for your tax prep software once a year. 

    Tuesday, June 23, 2020 11:48 AM
  • Hi Tom,

    I have found that LOGIN has a life cycle of 1 week ie 
    that in 1 week he will perform all the operations he usually performs 
    (It is a consumer goods store that connects to a central database).
    I know it's not 100% but I think that when I create BASELINE in 1 week (asi pomocí Extende Events nebo SQL Audit), I will have a high probability that I will not forget anything important.


    Lada vyvojar

    Tuesday, June 23, 2020 12:33 PM