Monday, April 25, 2011

SQL Server - Server Roles - XI


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 rolesYou 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 RoleDescription
sysadminCan perform any task in SQL Server.
serveradminCan set server-wide configuration options, can shut down the server.
setupadminCan manage linked servers and startup procedures.
securityadminCan manage logins and database permissions, read logs, change passwords.
processadminCan manage processes running in SQL Server.
dbcreatorCan create, alter, and drop databases.
diskadminCan manage disk files.
bulkadminCan execute BULK INSERT statements.
publicEvery 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