SQL Server Roles at Instance Level

There are two types of roles at SQL Server Instance level.

  1. Fixed Roles or Predefined roles.
  2. Customised roles or User defined roles.

The following are various fixed roles.

  1. bulkadmin  –> This role can perform bulk insert operations on database.
  2. dbcreator    –>  This role can perform to create/alter/drop/restore a database.
  3. diskadmin   –> Members of this role can manage disk files on server and all DB’s.
  4. process admin  –> Members of this role can manage and terminate/kill processes in                                        SQL Server.
  5. securityadmin –> Members of this role can manage all logins and resetting the                                                  passwords as needed.
  6. setup admin   –> Members of this role can add/remove linked servers.
  7. sysadmin          –>Members of this role can do all actions, it becomes admin rights.
  8. serveradmin     –> Members of this role can change server wide configurations and                                            shutdown the SQL Server.
  9. public                   –> This role is by default assigned to every login, it can do basic level                                          activities.

2. We can create our own customized roles.

Syntax for creating custom server role.

create server role <role-name> authorization <server-principal>

Example:

create server role myrole authorization bulkadmin;


For example we need to create one custom role and assigned to particular login that custom role contains bulkadmin,dbcreator and processadmin permissions.

 

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s