I’m writing a SQL Server security presentation and I need your help.

5

February 24, 2022 by Kenneth Fisher

I’m working on a new presentation titled

Watch Ken solve security headaches in SQL Server.

In this presentation I’m going to pick a handful of common security problems and solve them in demos. How many? Well, enough to fit the time frame. I’ll prepare at least 10, probably quite a few more and keep going until I run out of time.

This is a list of examples of SQL Server security related questions that I get fairly frequently and that I’ll probably be using.

  • Does account XYZ have execute access for stored procedure sp_blah?
  • What AD groups have read access in database DBName?
  • Please copy the permissions for account svcAccountA to svcAccountB.
  • Please copy all of the logins that are associated with DBName from ServerA to ServerB.
  • Please script the permissions for database DBName.
  • Can I have sysadmin access?
  • Jacky has the access I need in database DBName. What AD group should I be added to to get the same access that she has?

Here’s where I could use your help. What are some of the problems you have? Rather than just come up with things that I have to answer all the time, I’d like to know what types of security questions give you headaches?

5 thoughts on “I’m writing a SQL Server security presentation and I need your help.

  1. Andy says:

    Why can’t I reseed an identity column when I truncate a table via SSIS reload process.

  2. Someone says:

    It might be beyond the scope of your presentation, but authorizations involving linked servers (to other SQL instances) can get confusing. Especially when connections are made using a specific security context.

  3. Sean Redmond says:

    It’s not (yet) a problem but they get a bad press: linked servers.
    I find them extremely useful for reports. If they are not to be used, what are? SSIS-packages?

  4. I’d ask
    — Who can generate an execution plan?
    — Does the user have access to the sys files?
    — Can the user see the Reports Tab off of the server?
    — Can the user see all of the reports/links off of this tab

    These are current issues our DBAs need to resolve – generally you’d need a higher level of access to see these.

  5. Robert says:

    I would also include ways of unintentionally enabling privilege escalation, such as db owner being an sa account, or setting a user to security admin. Both allow a non-sa account to elevate their access to an sa. There are other ways that also exist. Knowing about them is probably the hardest part.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,709 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: