How can we help you today? How can we help you today?

Signs SQL Server permissions are in a mess...

What signs tell you your SQL Server permissions are in an unhealthy state? What are your tips and tricks when it comes to best practices?

So far we've got orphaned users - bad, sa users - bad, or employees who have left the company - really bad. What else?

Thanks,

Doerte

P.S: If you're at SQL Saturday on 8th September we can talk more.

Doerte Letzmann
0

Comments

5 comments

Sort by
  • ben_b
    you have definitely got the main ones, which I see all the time.

    sp_blitz has some good checks.  this one in particular:

    "[Servername] is configured as a linked server. Check its security configuration to make sure it isn't connecting with SA or some other bone-headed administrative login, because any user who queries it might get admin-level permissions."

    https://www.brentozar.com/blitz/linked-servers/
    ben_b
    0
  • ben_b
    p.s.  Kathi's article on Kerberos the other day was so helpful - something I had avoided learning about for years.  Will really help with trying to sort out the linked server quagmire I constantly find at places.

    cheers,
    Ben
    ben_b
    0
  • Doerte
    Thanks Ben! Those tips are great and we hadn't considered them yet. Anything else that's a definite red flag?
    Doerte
    0
  • ben_b
    ssis_admin role on the SSISDB database could be one, especially on sql 2012 as you need it to get data from the views in that database and run the ssis execution reports.

    Local admins on the server perhaps?  or people who can RDP onto the server?

    not sure if its relevant to what you are after but when it comes to permissions I always find that you pay the price for poorly setup environments when it comes to deploying ssis packages.  Being able to see what service accounts each of the services are running as for each server is really useful - and unless you can log onto the server, run DMVs or run powershell against the servers it can be a pain to collect all that information.

    so knowing which account your sql engine, sql agent and ssis service are running as really helps - especially when you deploy a ssis package and it works fine in dev but not in test/prod.
    ben_b
    0
  • brentm
    DBO is also too elevated and even DDL admin too. Our auditors always raise these red flags
    brentm
    0

Add comment

Please sign in to leave a comment.