Thanks for reply Eric but I actually wanted to know what are the tasks which can be performed by sysadmin only
like only a sysadmin can create a linked server. So what are other such tasks? Is there a way to figure that out?
thanks
Eric M Russell (10/27/2016)
By default, a user is in the PUBLIC role and cannot do anything. You then should grant them permissions piecemeal. There are probably 1,000 things a member of SYSADMIN can do that a PUBLIC user can't, and the list changes with each release of SQL Server.The following query will (basically, I think) return a list of high level permissions. I'd say use this list to have a conversation with the user and then both of you decide what they need to perform as part of their job role.
SELECT DISTINCT parent_class_desc, parent_covering_permission_name FROM fn_builtin_permissions(default);
For example:
...
SERVERAUTHENTICATE SERVER
SERVERCONTROL SERVER
SERVERCREATE ANY DATABASE
SERVERCREATE DDL EVENT NOTIFICATION
SERVERVIEW ANY DEFINITION
SERVERVIEW SERVER STATE
...
DATABASEDELETE
DATABASEEXECUTE
DATABASEINSERT
DATABASEREFERENCES
DATABASESELECT
DATABASEUPDATE
DATABASEVIEW DEFINITION
...
...
Online Trainer For SQL DBA and Developer @RedBushTechnologies with 18 yrs exp.