Authentication | SQL DBA learning curve

what are my privileges or rights on an SQL instance

April 9, 2013 GS Sohal 1 comment

“Boot down pill” / Biohacks / SML.20130205.IPH5.10051 (Photo credit: See-ming Lee 李思明 SML)

whoops!!! it happens we are given certain rights on SQL instance .. we tend to ask “WHAT are MY RIGHTS on THIS instance ??? ”

and you may be said “WELL FIND IT OUT yourSELF”

then we search for a PILL .. which could help us on the spot ..

fn_my_permissions is similar to the PILL .. that could work wonders for you ..

the results you get after running this will vary according to your access level on an instance

select * from fn_my_permissions (NULL,’SERVER’) — gives access details at server level

select * from fn_my_permissions (NULL,’Database”) — gives access details at DB level

list of privileges if your are sysadmin on a SQL server instance

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		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		ALTER ANY SERVER AUDIT
server		CONTROL SERVER

select * from fn_my_permissions (NULL,’DATABASE’)

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		ALTER ANY SERVER AUDIT
server		CONTROL SERVER

Enjoy 🙂