SQL SERVER – Get Permissions of My Username / Userlogin on Server / Database

A few days ago, I was invited to one of the largest database company. I was asked to review database schema and propose changes to it. There was special username or user logic was created for me, so I can review their database. I was very much interested to know what kind of permissions I was assigned per server level and database level. I did not feel like asking their Sr. DBA the question about permissions.

I run following two queries to know what are the permissions I am assigned at Server level and Database level.

SELECT * FROM fn_my_permissions(NULL, 'SERVER');
USE AdventureWorks;
SELECT * FROM fn_my_permissions (NULL, 'DATABASE');
GO

Results were interesting. After I came back from my visit, I ran above script on my personal database server, where I logged as SA.

Resultset for Server:
entity_name subentity_name permission_name
server CONNECT SQL
server SHUTDOWN
server CREATE ENDPOINT
server CREATE ANY DATABASE
server ALTER ANY LOGIN
server ALTER ANY CREDENTIAL
server ALTER ANY ENDPOINT
server ALTER ANY LINKED SERVER
server ALTER ANY CONNECTION
server ALTER ANY DATABASE
server ALTER RESOURCES
server ALTER SETTINGS
server ALTER TRACE
server ADMINISTER BULK OPERATIONS
server AUTHENTICATE SERVER
server EXTERNAL ACCESS ASSEMBLY
server VIEW ANY DATABASE
server VIEW ANY DEFINITION
server VIEW SERVER STATE
server CREATE DDL EVENT NOTIFICATION
server CREATE TRACE EVENT NOTIFICATION
server ALTER ANY EVENT NOTIFICATION
server ALTER SERVER STATE
server UNSAFE ASSEMBLY
server CONTROL SERVER

ResultSet for Database:
entity_name subentity_name permission_name
database CREATE TABLE
database CREATE VIEW
database CREATE PROCEDURE
database CREATE FUNCTION
database CREATE RULE
database CREATE DEFAULT
database BACKUP DATABASE
database BACKUP LOG
database CREATE DATABASE
database CREATE TYPE
database CREATE ASSEMBLY
database CREATE XML SCHEMA COLLECTION
database CREATE SCHEMA
database CREATE SYNONYM
database CREATE AGGREGATE
database CREATE ROLE
database CREATE MESSAGE TYPE
database CREATE SERVICE
database CREATE CONTRACT
database CREATE REMOTE SERVICE BINDING
database CREATE ROUTE
database CREATE QUEUE
database CREATE SYMMETRIC KEY
database CREATE ASYMMETRIC KEY
database CREATE FULLTEXT CATALOG
database CREATE CERTIFICATE
database CREATE DATABASE DDL EVENT NOTIFICATION
database CONNECT
database CONNECT REPLICATION
database CHECKPOINT
database SUBSCRIBE QUERY NOTIFICATIONS
database AUTHENTICATE
database SHOWPLAN
database ALTER ANY USER
database ALTER ANY ROLE
database ALTER ANY APPLICATION ROLE
database ALTER ANY SCHEMA
database ALTER ANY ASSEMBLY
database ALTER ANY DATASPACE
database ALTER ANY MESSAGE TYPE
database ALTER ANY CONTRACT
database ALTER ANY SERVICE
database ALTER ANY REMOTE SERVICE BINDING
database ALTER ANY ROUTE
database ALTER ANY FULLTEXT CATALOG
database ALTER ANY SYMMETRIC KEY
database ALTER ANY ASYMMETRIC KEY
database ALTER ANY CERTIFICATE
database SELECT
database INSERT
database UPDATE
database DELETE
database REFERENCES
database EXECUTE
database ALTER ANY DATABASE DDL TRIGGER
database ALTER ANY DATABASE EVENT NOTIFICATION
database VIEW DATABASE STATE
database VIEW DEFINITION
database TAKE OWNERSHIP
database ALTER
database CONTROL

Reference : Pinal Dave (https://blog.sqlauthority.com), BOL