View My Definition

Automated Permissions Auditing With Powershell and T-SQL: Part 5

If you’ve followed along to this point, and have assembled the pieces as I’ve given them to you, you’re now...

Security Queries: Database-level

A set of queries which attempt to gather as much security-related information on a single database as possible.

Security Queries: Server-level

A set of queries which attempt to gather as much security-related information on a server instance as possible.

Issues Determining an Individual SQL Server User's Permissions

Recently I was supporting a third party application. It queries to determine what tables it has permissions to before it proceeds with the rest of its functionality. We had implemented permissions based on the best practice of creating roles, assigning the permissions to the roles, and then making the users members of the roles. The application was querying INFORMATION_SCHEMA.TABLE_PRIVILEGES and of course didn't find any permissions directly against the user in question. We ended up granting explicit permissions to the user so the application would work, but I'm more interested in the general case. How can I determine permissions for an individual user?

Get Back into SQL Server After You've Locked Yourself Out

Someone, while locking down the SQL Server, removed the permissions by which the DBAs came in and administered the server. As a result, we cannot get back into SQL Server. How can we restore our access to SQL Server? Check out this tip to find out.

Automatically Scripting Out Permissions for Cross Environment Restores

This article from Warren Campbell shows a process to recreate permissions in development environments after restoring a database from a production instance.

Reverse-Engineer Database Permissions (2005 up)

This script reverse engineers SQL statements to recreate database permissions.

Stored procedure to script out user permissions

Stored procedure to script out user permissions

Retrieving SQL Server Permissions for Disaster Recovery

I'm needing to audit the permissions in my databases, but I want to script them out so I have something to run in case of a recovery situation. I've got the logins, roles, and users handled, but it's the permissions that I want to extract. How can I do this easily?