Search This Blog

Wednesday, July 30, 2008

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

No comments: