How to check my SQL permissions

27 May,2016 by Jack Vamvas

I received a phone call from a customer dealing with a t-sql  problem . I asked him to organise some access so I could troubleshoot the problem. When I  logged on I wanted to confirm the level of permissions.

An easy way to check for your self is to use the fn_my_permissions function. The fn_my_permissions always returns permissions in the security context of the logged in caller.

There are multiple options , but some quick ones to give a list of server and database level permissions are listed below.

 

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

 

entity_name      subentity_name              permission_name

server                   CONNECT SQL

server                   SHUTDOWN

server                   CREATE ENDPOINT

server                   CREATE ANY DATABASE

server                   CREATE AVAILABILITY GROUP

server                   ALTER ANY LOGIN

 

 

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

 

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

 

Read More on SQL Server  security management

Troubleshoot SQL Security Error Ring Buffer Recorded -

SQL Server Security Policy (SQL Server DBA)

Database Security Countermeasures against hacker attacks (SQL ...


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to check my SQL permissions


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer