How to find my login account privileges in SQL Server
Every user wants to know his level of access on SQL Server if he is familiar with working on SQL Server management Studio.If we run below query we can find out the server level and database level privileges of a user.
select * from fn_my_permissions(null,'server')
/*above will give server level privileges of the login account */
go
select * from fn_my_permissions(null,'database')
/*above will give current database level privileges of the login account */
Output will be something like below:
As my login was 'SA', output is showing all the existing privileges for my account.
Being an DBA, i do get request where i need login with 'user account' and check privileges and execute any particular procedure with 'user account' privileges not with my 'SA' account.
So how to proceed in this case of scenarios
Use dbname
Go
Exec as login = 'domain\useraccount'
Go
/*above statement when executed, this particular session will move from 'SA' admin account to 'domain\useraccount' .
We can check now who is logged in */
SELECT @@SERVERNAME AS [SERVER NAME],SUSER_SNAME() AS [LOGIN NAME],DB_NAME() AS [DATABASE NAME],USER_NAME() AS [USER NAME]
Go
/* Now you are logged into domain\useraccount */
/* If you want to rever back to the admin account, you have to execute revert command by logging into the same database where you have executed "exec as login" command */
Revert;
Go
OverAll server Level Privileges for your information:
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
Over All database level privileges for your information:
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
Hope the above information provided helps
Thanks
