Breaking News

Monday, July 9, 2012

Lesson10: SQL Server - Server Roles



When creating a new user login in SQL Server, you get the option of assigning the login one or more server roles.

Server roles (not to be confused with database roles) are available for various database administration tasks. Not everyone should be assigned to a server role. In fact, only advanced users such as database administrators should be assigned a server role.

Accessing the Server Roles

To access the server roles in SQL Server Management Studio, expand the Security folder:


Screenshot of accessing server roles


You view the properties of a server role by right clicking on it. You can then add users to the server role by clicking Add. In the screenshot below, Homer has been added to the securityadmin role.


Adding a user to a server role


Explanation of Server Roles

Here's an explanation of the server roles defined in SQL Server 2008 during setup:

Server Role
Description
sysadmin
Can perform any task in SQL Server.
serveradmin
Can set server-wide configuration options, can shut down the server.
setupadmin
Can manage linked servers and startup procedures.
securityadmin
Can manage logins and database permissions, read logs, change passwords.
processadmin
Can manage processes running in SQL Server.
dbcreator
Can create, alter, and drop databases.
diskadmin
Can manage disk files.
bulkadmin
Can execute BULK INSERT statements.
public
Every SQL Server user account belongs to this server role. When a server principal has not been granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object. Only assign public permissions on an object when you want the object to be available to all users.



As you can see, some of these roles allow very specific tasks to be performed. If you don't have many technical users, it's likely that you'll only use one or two of these roles (including sysadmin).

No comments:

Post a Comment

Designed By Published.. Blogger Templates