Server level permissions for SQL Server 2005 and 2008
March 25th 2009 05:14
Server level permissions for SQL Server 2005 and 2008
Problem
I am new to SQL Server 2005/2008 having administered SQL Server 2000 and below. I have heard there are a lot of new permissions at the server level. What are they and what do they give rights to perform?
Solution
SQL Server 2005 introduced a new concept to SQL Server security and permissions: securables. Securables are anything within SQL Server that can have a permission assigned. One such securable is the server.
Below are the list of server-level permissions:
Permission - ADMINISTER BULK OPERATIONS
Effect - Grants or denies the ability to execute BULK INSERT commands. However, INSERT permissions must exist for the login with respect to the table being loaded. In addition, ALTER TABLE permissions may also be required. The bulkadmin fixed server role is granted this permission implicitly.
Permission - ALTER ANY CONNECTION
Effect - Grants or denies the ability to manage existing connections to SQL Server such as through the use of the KILL reserved word. The processadmin fixed server role has this permission granted implicitly.
Permission - ALTER ANY CREDENTIAL
Effect - Grants or denies the ability to manage credentials. Credentials are Windows user account/password combinations which can used outside of SQL Server. They are used with SQL Server Agent jobs to set security context for a job running outside of SQL Server.
Permission - ALTER ANY DATABASE
Effect - Grants or denies the ability to drop or modify existing databases as well as create new databases. The dbcreator fixed server role has this permission granted implicitly.
Permission - ALTER ANY ENDPOINT
Effect - Grants or denies the ability to create, drop, or modify endpoints. The sysadmin fixed server role has this permission granted implicitly.
Permission - ALTER ANY EVENT NOTIFICATION
Effect - Grants or denies the ability to create any event notification using CREATE EVENT NOTIFICATION with respect to Service Broker.
Permission - ALTER ANY LINKED SERVER
Effect - Grants or denies the ability to create, drop, or modify linked server connections.
Permission - ALTER ANY LOGIN
Effect - Grants or denies the ability to create, drop, or modify logins. To affect a login which is a member of the sysadmin fixed server role or a login which has been granted CONTROL SERVER permissions, you must also have CONTROL SERVER permissions. The securityadmin fixed server role has this permission granted implicitly.
Permission - ALTER ANY RESOURCES
Effect - Grants or denies the ability to manage disk resources for SQL Server. The diskadmin and serveradmin fixed server roles are granted this permission implicitly.
Permission - ALTER SERVER STATE
Effect - Grants or denies the ability to manage some aspects of the server, such as DBCC
FREEPROCCACHE,and DBCC FREESYSTEMCACHE.
Permission - ALTER SETTINGS
Effect - Grants or denies the ability to execute sp_configure and change settings. Also grants or denies the ability to execute RECONFIGURE afterwards. This permissions is granted by default to members of the serveradmin and sysadmin fixed server role.
Permission - ALTER TRACE
Effect - Grants or denies the ability to execute a server-side or SQL Server Profiler trace. Without this permission, the only logins capable of running traces are members of the sysadmin fixed server role.
Permission - AUTHENTICATE SERVER
Effect - Grants or denies the ability to use a particular signature across all databases on the server when impersonation is used..
Permission - CONNECT SQL
Effect - Grants or denies the ability to connect to the SQL Server. All logins, when newly created, are granted this permission automatically.
Permission - CONTROL SERVER
Effect - Grants or denies the ability to do anything on the SQL Server. The sysadmin fixed server role has this permission granted implicitly.
Permission - CREATE ANY DATABASE
Effect - Grants or denies the ability to create new databases. The dbcreator fixed server role has this permission granted implicitly.
Permission - CREATE DDL EVENT NOTIFICATION
Effect - Grants or denies the ability to create a DDL event notification using CREATE EVENT NOTIFICATION with respect to Service Broker.
Permission - CREATE ENDPOINT
Effect - Grants or denies to create an endpoint, a connection into SQL Server. The serveradmin and sysadmin fixed server roles has this permission granted implicitly.
Permission - CREATE TRACE EVENT NOTIFICATION
Effect - Grants or denies the ability to create a trace event notification using CREATE EVENT NOTIFICATION with respect to Service Broker.
Permission - EXTERNAL ACCESS ASSEMBLY
Effect - Grants or denies the ability to add CLR assemblies to SQL Server that have a permissions set = EXTERNAL_ACCESS.
Permission - SHUTDOWN
Effect - Grants or denies the ability to shutdown the SQL Server service. The sysadmin and serveradmin fixed servers have this permission implicitly.
Permission - UNSAFE ASSEMBLY
Effect - Grants or denies the ability to add CLR assemblies to SQL Server that have a permissions set = UNSAFE.
Permission - VIEW ANY DATABASE
Effect - Grants or denies the ability to see metadata on databases through sys.databases, sysdatabases, or sp_helpdb. The public fixed server role has this permission implicitly (meaning anyone who can connect to the SQL Server instance).
Permission - VIEW ANY DEFINITION
Effect - Grants or denies the abilities to see the T-SQL code and any metadata for any object within the server.
Permission - VIEW SERVER STATE
Effect - Grants or denies the ability to see server level configuration information. The serveradmin fixed server role has this permission implicitly.
While all of these server level permissions are important, some of the ones to pay particular attention to are:
CONTROL SERVER - can do anything within SQL Server
SHUTDOWN - can shutdown the SQL Server service
ALTER SETTINGS - can run sp_configure and change the configuration of SQL Server.
ALTER ANY LOGIN - can modify logins to SQL Server
ALTER ANY DATABASE - can modify databases in SQL Server.
CREATE ENDPOINT - can create new connection points for SQL Server.
ALTER TRACE - Can execute traces against the SQL Server.
----------------------------- ----------------------------- ----------------------
Listing Permissions
A quick and easy script you can use to see what permissions are assigned at the server level is the following. It uses the sys.server_permissions catalog view joined against the sys.server_principals catalog view to pull back all the server-level permissions belonging to SQL Server logins, Windows user logins, and Windows group logins:
SELECT
[srvprin].[name] [server_principal],
[srvprin].[type_desc] [principal_type],
[srvperm].[permission_name],
[srvperm].[state_desc]
FROM [sys].[server_permissions] srvperm
INNER JOIN [sys].[server_principals] srvprin
ON [srvperm].[grantee_principal_id] = [srvprin].[principal_id]
WHERE [srvprin].[type] IN ('S', 'U', 'G')
ORDER BY [server_principal], [permission_name];
server_principal principal_type permission_name state_desc
DBUser1 SQL_LOGIN CONNECT SQL GRANT
DBUser1 SQL_LOGIN CONTROL SERVER GRANT
----------------------------- ----------------------------- ----------------------
Granting Permissions
Granting rights is pretty straight forward. To grant "SHUTDOWN" rights to login "DBUser1" you would issue the following command:
GRANT SHUTDOWN TO DBUser1
Problem
I am new to SQL Server 2005/2008 having administered SQL Server 2000 and below. I have heard there are a lot of new permissions at the server level. What are they and what do they give rights to perform?
Solution
SQL Server 2005 introduced a new concept to SQL Server security and permissions: securables. Securables are anything within SQL Server that can have a permission assigned. One such securable is the server.
Below are the list of server-level permissions:
Permission - ADMINISTER BULK OPERATIONS
Effect - Grants or denies the ability to execute BULK INSERT commands. However, INSERT permissions must exist for the login with respect to the table being loaded. In addition, ALTER TABLE permissions may also be required. The bulkadmin fixed server role is granted this permission implicitly.
Permission - ALTER ANY CONNECTION
Effect - Grants or denies the ability to manage existing connections to SQL Server such as through the use of the KILL reserved word. The processadmin fixed server role has this permission granted implicitly.
Permission - ALTER ANY CREDENTIAL
Effect - Grants or denies the ability to manage credentials. Credentials are Windows user account/password combinations which can used outside of SQL Server. They are used with SQL Server Agent jobs to set security context for a job running outside of SQL Server.
Permission - ALTER ANY DATABASE
Effect - Grants or denies the ability to drop or modify existing databases as well as create new databases. The dbcreator fixed server role has this permission granted implicitly.
Permission - ALTER ANY ENDPOINT
Permission - ALTER ANY EVENT NOTIFICATION
Effect - Grants or denies the ability to create any event notification using CREATE EVENT NOTIFICATION with respect to Service Broker.
Permission - ALTER ANY LINKED SERVER
Effect - Grants or denies the ability to create, drop, or modify linked server connections.
Permission - ALTER ANY LOGIN
Effect - Grants or denies the ability to create, drop, or modify logins. To affect a login which is a member of the sysadmin fixed server role or a login which has been granted CONTROL SERVER permissions, you must also have CONTROL SERVER permissions. The securityadmin fixed server role has this permission granted implicitly.
Permission - ALTER ANY RESOURCES
Effect - Grants or denies the ability to manage disk resources for SQL Server. The diskadmin and serveradmin fixed server roles are granted this permission implicitly.
Permission - ALTER SERVER STATE
Effect - Grants or denies the ability to manage some aspects of the server, such as DBCC
FREEPROCCACHE,and DBCC FREESYSTEMCACHE.
Permission - ALTER SETTINGS
Effect - Grants or denies the ability to execute sp_configure and change settings. Also grants or denies the ability to execute RECONFIGURE afterwards. This permissions is granted by default to members of the serveradmin and sysadmin fixed server role.
Permission - ALTER TRACE
Effect - Grants or denies the ability to execute a server-side or SQL Server Profiler trace. Without this permission, the only logins capable of running traces are members of the sysadmin fixed server role.
Permission - AUTHENTICATE SERVER
Effect - Grants or denies the ability to use a particular signature across all databases on the server when impersonation is used..
Permission - CONNECT SQL
Effect - Grants or denies the ability to connect to the SQL Server. All logins, when newly created, are granted this permission automatically.
Permission - CONTROL SERVER
Effect - Grants or denies the ability to do anything on the SQL Server. The sysadmin fixed server role has this permission granted implicitly.
Permission - CREATE ANY DATABASE
Effect - Grants or denies the ability to create new databases. The dbcreator fixed server role has this permission granted implicitly.
Permission - CREATE DDL EVENT NOTIFICATION
Effect - Grants or denies the ability to create a DDL event notification using CREATE EVENT NOTIFICATION with respect to Service Broker.
Permission - CREATE ENDPOINT
Effect - Grants or denies to create an endpoint, a connection into SQL Server. The serveradmin and sysadmin fixed server roles has this permission granted implicitly.
Permission - CREATE TRACE EVENT NOTIFICATION
Effect - Grants or denies the ability to create a trace event notification using CREATE EVENT NOTIFICATION with respect to Service Broker.
Permission - EXTERNAL ACCESS ASSEMBLY
Effect - Grants or denies the ability to add CLR assemblies to SQL Server that have a permissions set = EXTERNAL_ACCESS.
Permission - SHUTDOWN
Effect - Grants or denies the ability to shutdown the SQL Server service. The sysadmin and serveradmin fixed servers have this permission implicitly.
Permission - UNSAFE ASSEMBLY
Effect - Grants or denies the ability to add CLR assemblies to SQL Server that have a permissions set = UNSAFE.
Permission - VIEW ANY DATABASE
Effect - Grants or denies the ability to see metadata on databases through sys.databases, sysdatabases, or sp_helpdb. The public fixed server role has this permission implicitly (meaning anyone who can connect to the SQL Server instance).
Permission - VIEW ANY DEFINITION
Effect - Grants or denies the abilities to see the T-SQL code and any metadata for any object within the server.
Permission - VIEW SERVER STATE
Effect - Grants or denies the ability to see server level configuration information. The serveradmin fixed server role has this permission implicitly.
While all of these server level permissions are important, some of the ones to pay particular attention to are:
CONTROL SERVER - can do anything within SQL Server
SHUTDOWN - can shutdown the SQL Server service
ALTER SETTINGS - can run sp_configure and change the configuration of SQL Server.
ALTER ANY LOGIN - can modify logins to SQL Server
ALTER ANY DATABASE - can modify databases in SQL Server.
CREATE ENDPOINT - can create new connection points for SQL Server.
ALTER TRACE - Can execute traces against the SQL Server.
----------------------------- ----------------------------- ----------------------
Listing Permissions
A quick and easy script you can use to see what permissions are assigned at the server level is the following. It uses the sys.server_permissions catalog view joined against the sys.server_principals catalog view to pull back all the server-level permissions belonging to SQL Server logins, Windows user logins, and Windows group logins:
SELECT
[srvprin].[name] [server_principal],
[srvprin].[type_desc] [principal_type],
[srvperm].[permission_name],
[srvperm].[state_desc]
FROM [sys].[server_permissions] srvperm
INNER JOIN [sys].[server_principals] srvprin
ON [srvperm].[grantee_principal_id] = [srvprin].[principal_id]
WHERE [srvprin].[type] IN ('S', 'U', 'G')
ORDER BY [server_principal], [permission_name];
server_principal principal_type permission_name state_desc
DBUser1 SQL_LOGIN CONNECT SQL GRANT
DBUser1 SQL_LOGIN CONTROL SERVER GRANT
----------------------------- ----------------------------- ----------------------
Granting Permissions
Granting rights is pretty straight forward. To grant "SHUTDOWN" rights to login "DBUser1" you would issue the following command:
GRANT SHUTDOWN TO DBUser1
| 28 |
| Vote |
Add To: 
