SQL Server Level Permissions

In SQL 2005, there are various server level permissions which can be provided to any login or role and the same can be provided by the GRANT statement executed from the master database.

If executed at different database this is the error received.

Server: Msg 4621, Level 16, State 10, Line 1
Permissions at the server scope can only be granted when the current database is master

USE master
GRANT CONTROL SERVER TO GaneshJayaraman
GO

Permissions which can be provided are as follows:
ADMINISTER BULK OPERATIONS
ALTER ANY CONNECTION
ALTER ANY CREDENTIAL
ALTER ANY DATABASE
ALTER ANY ENDPOINT
ALTER ANY EVENT NOTIFICATION
ALTER ANY LINKED SERVER
ALTER ANY LOGINALTER RESOURCES
ALTER SERVER STATE
ALTER SETTINGS
ALTER TRACE
AUTHENTICATE SERVER
CONNECT SQL
EXTERNAL ACCESS ASSEMBLY
SHUTDOWN
UNSAFE ASSEMBLY
VIEW ANY DEFINITION
However these all are implicitly provided when CONTROL SERVER permission is available

CREATE ANY DATABASE
When the same permission is provided ALTER ANY DATABASE rights are also provided

CREATE DDL EVENT NOTIFICATION
When the same permission is provided ALTER ANY EVENT NOTIFICATION rights are also provided

CREATE ENDPOINT
When the same permission is provided ALTER ANY ENDPOINT rights are also provided

CREATE TRACE EVENT NOTIFICATION
When the same permission is provided ALTER ANY EVENT NOTIFICATION rights are also provided

VIEW ANY DATABASE
When the same permission is provided VIEW ANY DEFINITION rights are also provided

VIEW SERVER STATE
When the same permission is provided ALTER SERVER STATE rights are also provided

These are all very useful as the user can be given specific permission instead of providing "sa" and is useful when new databases are added, as DB permission exists for all the DBs in the server

Server Settings

Server Settings Dialog has changed a bit in SQL 2005 when compared to SQL 2000,let us view the graphical digram to have a look and feel of how SQL 2005 is different.

SQL 2000 Server Properties from SQL 2000 Enterprise Manager

SQL 2005 Server Properties from Management Studio

Here we can see tab format is changed to side panels format all the side panel menus are almost similar to tabs except for Advanced properties which is present in SQL 2005,which has it got its name renamed from SQL 2005 Server Settings tab.

And the next main new panel introduced is Permissions where various server level permissions are provided to various logins and roles