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:
Post a Comment