Mini Post: Showing Permissions Granted To A User, Via T-SQL

Hey everyone! Welcome back to SQL Something!

Today we are going to take a quick look at how to view the available permissions a user was granted. I needed this the other day when I wanted to create a user with similar permissions for another DB. Luckily fn_my_permissions has all the answers we need.

We can query fn_my_permissions to find out permissions info at a DB level like so:

USE YourDBName;
SELECT *
FROM fn_my_permissions (NULL, 'DATABASE');

This, however, will list the permissions for the current user doing the query (which might not actually be the user you want the info for).

To find permissions for a specific user, you must first impersonate that user like so:

USE YourDBName;
EXECUTE AS USER 'User1';
SELECT *
FROM fn_my_permissions (NULL, 'DATABASE');
REVERT;

Please note that using the EXECUTE command will give you only the permissions of the user you are impersonating. You need the REVERT command at the end of the query to give yourself back the permissions you previously had.

DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)

Getting Database Creation Date Via T-SQL

Hey everyone! Welcome back to SQL Something!

The first post for April is going to be pretty simple. Today we are going to look at how to retrieve the date that a database was created via T-SQL. Could be useful in some situations. I, for instance, just wanted to know when a DB was swapped out for a empty one. The new empty DB wasn't put to use the same day/time it was created, so the timestamps of the actual data in the DB didn't exactly provide me with what I wanted.

Anyway, I digress. Let's look at what sys.databases can do in order to help with our problem.