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.
- Use Cases #1: First use case as stated above, understand what permissions a user has to the SQL infrastructure.
- Use Case #2: Full Security Output for a Single Server
- 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.
SQL Canada (SQLCAN) 