SQLOpsDB Reports: Security Lookup

Ever had a person leave the organization or your team and wanting to know where do they have access? What explicit permissions, logins, or database users you have to clean up?

If are like many organizations with 100s, 200s, or eve 300+ instances, this is an impossible task.

The SQLOpsDB solution collects all security details for the server and each database. The report “Security – User Lookup” is designed to leverage the data collection to provide this insight to your environment.

  1. Use Cases #1: First use case as stated above, understand what permissions a user has to the SQL infrastructure.
    1. Page 1/3 – User with “domain” what permissions doe sit have all on servers.
    2. Page 2/3 – What explicit permissions on server level?
    3. Page 3/3 – What database access and permissions it has?
  2. Use Case #2: Full Security Output for a Single Server
    1. Page 1/4: Sever Logins and Role Membership
    2. Page 2/4: Server Explicit Permissions
    3. Page 3/4: Database Membership
    4. Page 4/4: Database Explicit Permissions
  3. Use Case #3: Full Security Dump

When you launch the report, first supply the required parameters.

  • User/Login Name
    • Cannot be blank. Users LIKE search, so can use SQL Server wild characters matching.
    • Can be an targeted search, “%admin%” (without quotes).
    • Can be full security dump “%”.
  • Server Instance
    • Can target all instances or a single instance.

You will notice certain defaults are missing. Such as dbo, sa, or connect permissions. I tried to minimize collection by removing common accounts and permissions.

Use Cases #1: First use case as stated above, understand what permissions a user has to the SQL infrastructure.

Page 1/3 – User with “domain” what permissions doe sit have all on servers.

Page 2/3 – What explicit permissions on server level?

Page 3/3 – What database access and permissions it has?

Use Case #2: Full Security Output for a Single Server

Maybe you need an audit report? Who has access to all the database and server on a single instance?

Page 1/4: Sever Logins and Role Membership

Page 2/4: Server Explicit Permissions

Page 3/4: Database Membership

Page 4/4: Database Explicit Permissions

Use Case #3: Full Security Dump

All though this scenario is possible and I do not prevent it, I would not recommend it. I have not tested this on a large infrastructure.

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.