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?  Read more...

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.  Read more...

Reports permissions to three levels: database, schema and table -- in three formats: readable, structured, scripted.  Read more...

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

This script reverse engineers SQL statements to recreate database permissions.  Read more...

I often need to synchronize the user permission between databases. I wrote this simple query to script the sql statement to per item.   Read more...

Scripts role membership, object-level permissions for all DB users.  Read more...

Stored procedure to script out user permissions  Read more...

Scripts role membership, object-level permissions for all DB users.  Read more...

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?  Read more...

The script provides complete and comprehensive permission reports, on all SQL Server security hierarchy levels, for all Logins. Reports on particular Logins and databases can be customized.  Read more...

Read more...

The script provides complete and comprehensive permission reports, on all SQL Server security hierarchy levels, for all Logins. Reports on particular Logins and databases can be customized.  Read more...

Whenever I do a security presentation, I make sure to cover the Principle of Least Privilege. And when I do I boil it down to this very simple definition: Giving the rights to do the job. No more. No less.  Read more...

Create A role and grant it a permission on a DB Schemas.  Read more...

I am new to SQL Server 2005/2008 having administered SQL Server 2000 and below. I have heard there are a lot of new permissions at the database level. What are they and what do they give rights to perform?  Read more...

This script will walk the hierarchical chain of users and roles to find users with permissions on an object.  Read more...

This article shows the steps to analyze SQL Server logins and permissions to ensure that there are no logins and users that exist who are have unnecessary access  Read more...

In a previous blog post on Detecting When a Login Has Implicit Access to a Database, I mentioned that having CONTROL SERVER rights means having implicit rights into the databases. Robert Davis posted a comment asking if there was a difference with respect to explicit permissions between being a member of the sysadmin fixed server role and having CONTROL SERVER rights.  Read more...

Yan Pan explains how to set up proxies in SQL Server 2000, 2005 and 2008, and compares the differences between them  Read more...