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.

Mini Post: Getting A List of Connections By IP Address

Hello and welcome back to SQL Something!

I was looking at how to view current connections the other day (much like this past blog post) when I think I saw someone mention checking SQL connections by IP address. I thought this would be pretty cool if it was possible, and after some light searching I found this nice, succinct post by Glenn Berry.

I won't post the query here, but suffice to say it does the job very well.

It uses fields found in the sys.dm_exec_connections and sys.dm_exec_sessions views in order to display the client_net_address (the IP address), the host_name and the login_name as well as a count of session_id per login which provides a connection count per login. Awesome sauce.

Finally, there is a second query in the original post by Mr. Berry that gives you strictly a count by login name using only the sys.dm_exec_connections, which is neat (though honestly I'm not too sure how useful; If you can think of a great way the second query can be used, please let me know by commenting).

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. :-)

Mini Post: SQL Replication - Changing The Default Snapshot Folder

Hello and good day, wherever you are! Welcome back to SQL Something!

Today's Mini Post is how to change your default snapshot folder for SQL replication. A snapshot is necessary to initially initialize the database that the replicated data is being sent to. So let's look at the process, quickly and with pictures!

Pictures make everything better.

Mini Post: Group By Month (or Year)

Hello to all of you out there! Welcome back to SQL Something!

Today's Mini Post is showing how to group by a date part (such as month, year) when given a start and end date.

Today specifically we are going to group by month in the following example:

SELECT DATENAME(month, YourDateField) MonthName,
                DATEPART(month, YourDateField) MonthNumber,
                COUNT (YourField) FieldTotal
FROM YourTable
WHERE (YourDateField >= '01/01/2013' and YourDateField < '01/01/2014')
 -- AND add other criteria here
GROUP BY DATENAME(month, YourDateField),
                     DATEPART(month, YourDateField)
ORDER BY MonthNumber;

And that's all there is too it. :-)


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. :-)

Product Spotlight: Idera's SQL Virtual Database

Hey everyone and welcome to (and/or welcome back, hopefully) to SQL Something!

Today I'm trying something a little different. Today I'm going to take a look at a specific SQL related product for the first time. I honestly don't know how often I will be doing these but we'll see!

So did you ever wish you could just query a backup? Yes, just like a DB but without having to restore it? Yes, without restoring it, so you don't have to cater for additional space for the restore or cater for the time taken to perform the restore.

Not possible?

It is with Idera's SQL Virtual Database.

Fig. 1: Main Screen.

Mini Post: The Process Could Not Execute sp_replcmds

Good day out there and welcome back to SQL Something!

Today we're going to look at a small error which is related to the permissions on a DB that is being used for publishing in SQL replication.

After setting up my publication and a subscriber to be used for transactional replication, I noticed that transactions were not being replicated. Upon checking the "View Log Agent Status" (right click the publication and choose the option), I saw the following error: "The process could not execute sp_replcmds".

Fig. 1: The Error in Log Reader Agent. It can also be found in the Replication Monitor, under the 'Agents' tab.