This portal is to share my acquired knowledge related to Sql Server. I hope will be helpful for novice and professionals.

Wednesday, December 29, 2010

Server Roles in Sql Server 2005

Server Role is used to grant server-wide security privileges to a user.

Sql Server 2005 comes with following fixed server Roles are:
1. bulkadmin
 2. dbcreator
3. diskadmin
4. processadmin
5. securityadmin
6. serveradmin
7. setupadmin
8. sysadmin

Each Role has different power to play with SQL Server Instance,Like Role dbcreator can create, alter,

drop, and restore any database. It is not easy to remember that what a Role can do all.
Sql Server provides a System stored procedure(sp_srvrolepermission) to list the permissions of Server Roles.

Try this statement on any database:
EXEC sp_srvrolepermission dbcreator

Will list the all permissions associated with dbCreator Server Role in tabular form.
To get list of permissions for all Roles, run SP without parameter.

Using this SP we can easily understand all Server Roles and make a better Decision to implement security.

No comments: