This query lists the permissions of the connected user has on the database.

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

This function is available since SQL Server 2008. It can retrieve a lot of interesting information, see the Microsoft documentation page about it