Thursday
Dec162010

Determine SQL Permissions

Here's a list of queries you can run to determine who has access to what.  (Note that these work on SQL 2008.)

First find out who has membership in the server-wide roles (in the context of the example below, the dbo_owner role):

Next determine what membership may be carried over by membership in a Windows domain group:

Finally the last query will reveal what tokens are carried over by a specific user credential - regardless of whether that user has explicit permissions mapped in the database:

This last should reveal any lingering permissions from rights not explicitly defined in the database (e.g. rights granted to DOMAIN\Domain Users).

A gentle reminder: server-level permissions will allow login to the server, but any permissions at the database level, even when orphaned, may allow/deny additional access, so be sure to clean up those orphaned database-level logins!