Learn more about SQL Server tools

   
   















































Server level permissions for SQL Server 2005 and SQL Server 2008

MSSQLTips author K. Brian Kelley By:   |   Read Comments (14)   |   Related Tips: More > Security

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 Effect

ADMINISTER BULK OPERATIONS

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.

ALTER ANY CONNECTION

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.

ALTER ANY CREDENTIAL

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.

ALTER ANY DATABASE

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.
ALTER ANY ENDPOINT Grants or denies the ability to create, drop, or modify endpoints. The sysadmin fixed server role has this permission granted implicitly.

ALTER ANY EVENT NOTIFICATION

Grants or denies the ability to create any event notification using CREATE EVENT NOTIFICATION with respect to Service Broker.
ALTER ANY LINKED SERVER Grants or denies the ability to create, drop, or modify linked server connections.
ALTER ANY LOGIN 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.

ALTER ANY RESOURCES

Grants or denies the ability to manage disk resources for SQL Server. The diskadmin and serveradmin fixed server roles are granted this permission implicitly.

ALTER SERVER STATE

Grants or denies the ability to manage some aspects of the server, such as DBCC FREEPROCCACHE,and DBCC FREESYSTEMCACHE.

ALTER SETTINGS

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.

ALTER TRACE

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.

AUTHENTICATE SERVER

Grants or denies the ability to use a particular signature across all databases on the server when impersonation is used..

CONNECT SQL

Grants or denies the ability to connect to the SQL Server. All logins, when newly created, are granted this permission automatically.

CONTROL SERVER

Grants or denies the ability to do anything on the SQL Server. The sysadmin fixed server role has this permission granted implicitly.

CREATE ANY DATABASE

Grants or denies the ability to create new databases. The dbcreator fixed server role has this permission granted implicitly.

CREATE DDL EVENT NOTIFICATION

Grants or denies the ability to create a DDL event notification using CREATE EVENT NOTIFICATION with respect to Service Broker.

CREATE ENDPOINT

Grants or denies to create an endpoint, a connection into SQL Server. The serveradmin and sysadmin fixed server roles has this permission granted implicitly.

CREATE TRACE EVENT NOTIFICATION

Grants or denies the ability to create a trace event notification using CREATE EVENT NOTIFICATION with respect to Service Broker.

EXTERNAL ACCESS ASSEMBLY

Grants or denies the ability to add CLR assemblies to SQL Server that have a permissions set = EXTERNAL_ACCESS.

SHUTDOWN

Grants or denies the ability to shutdown the SQL Server service. The sysadmin and serveradmin fixed servers have this permission implicitly.

UNSAFE ASSEMBLY

Grants or denies the ability to add CLR assemblies to SQL Server that have a permissions set = UNSAFE.

VIEW ANY DATABASE

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).

VIEW ANY DEFINITION

Grants or denies the abilities to see the T-SQL code and any metadata for any object within the server.

VIEW SERVER STATE

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];


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

 

Next Steps



Last Update: 3/24/2009


About the author
MSSQLTips author K. Brian Kelley
K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Sunday, October 27, 2013 - 11:25:00 PM - K. Brian Kelley Read The Tip

Rakesh, the account you're using doesn't have the rights to execute that GRANT statement.


Thursday, October 24, 2013 - 5:09:18 AM - Rakesh Read The Tip

Hi ,

 

I want to provide execute permissions to a login on sp_readerrorlog.

 

I have executed the below command:

 

grant execute on sp_Readerrorlog to [login_name]

 

but it throws an error:

 

Login [login_name] should be a member of security admin server role in order to execute this query.

 

Please suggest.

Thank you.


Thursday, February 28, 2013 - 9:30:17 AM - K. Brian Kelley Read The Tip

OPENROWSET() allows you to pass queries through a linked server connection, but to do what you're trying to do is a real kludge. It would be better to connect directly to the SQL Server in question and make the change.


Wednesday, February 27, 2013 - 10:03:08 PM - Peter C Tran Read The Tip

Hi, I want to do an ALTER LOGIN against a linked server.  can i do that?

for example:

-- step 1. do alter login in my Primary server (im logged into)

ALTER LOGIN <loginID> WITH PASSWORD='somePassword>

-- step 2. do alter login against my Failover server (a linked server done by sp_addservers)

can i do this and what is syntax for the ALTER LOGIN command?

 

Thanks!
Peter


Monday, January 07, 2013 - 12:14:49 PM - Vince Read The Tip
Hi there,I've been having disk pmforreance problems with SCCM 2007 in an ESX 4 environment.The environment is well designed with every function (APP, DB, repositiories) are all on different virtual machines. I do not own the app but I think there are around 5000 users on it so far.The virtual storage is all NFS.The entire env is Windows 2008 R2 64bit. The main primary app server and its database have been reporting disk IO issues such as Average Disk Queue Length > 2 and Average disk sec/transfer outside tolerance levels.Microsoft have analyzied the perform logs and advise that the problem is the disk.The netapp storage guys tell me that storage is functioning very well with no problems. The ESX and IP SAN guys also say the same thing but Windows 2008 continues to complain about the disk.If the disk and network(remember NFS) are ok ..then why am I seeing DISK IO issues in the guest OS! VMWARE have drawn a blank although the tools to monitor NFS seem to be very poor indeed ..meaning they seem to be non-existent!

Friday, December 07, 2012 - 8:38:05 PM - yahlowgrin Read The Tip

Hi Brian,

Thank you for your prompt confirmation. =)


Friday, December 07, 2012 - 1:11:33 PM - K. Brian Kelley Read The Tip

Yes, [ADMINISTER BULK OPERATIONS] only grants the ability to execute BULK INSERT. You must have other permissions to do other things. For instance, it doesn't give you permission to enter a database nor does it give you permission to touch a table you don't normally have access to.

 


Friday, December 07, 2012 - 4:10:23 AM - yahlowgrin Read The Tip

[ADMINISTER BULK OPERATIONS]  - Grants or denies the ability to execute BULK INSERT commands

Hi, may I know if [ADMINISTER BULK OPERATIONS] ONLY grants or denies the ability to execute BULK INSERT commands and nothing else?

Is there any security issue with granting [ADMINISTER BULK OPERATIONS]?


Thursday, April 05, 2012 - 9:44:07 AM - K. Brian Kelley Read The Tip

Arjun, the question isn't related to the article, but the simple answer is you don't. As a DBA you probably want to be aware of what's going on and at the very least, launch DTA. If they are a db_owner of their database, they can use DTA on their database once you've started DTA.

 

http://msdn.microsoft.com/en-us/library/ms190987(v=sql.105).aspx

 


Wednesday, April 04, 2012 - 8:49:05 PM - Arjun Read The Tip

Hi ,

Thank you for the nice article.

 

I have a question here. How can we provide a user (developer) to execute Database Engine Tuning Advisor. We don't supposed to give the user sysadmin permissions for the user to execute DTA.

 

How to achieve this?

Thanks,

Arjun


Thursday, June 09, 2011 - 9:47:43 AM - Lelo121 Read The Tip

Hi,

I am fairly new on SQL Server so please excuse if I strile you as ignorant... Would you please explain the relationship between the stored procedure sp_srvrolepermission and the results of the query listed above?

When I run the stored procedure I notice that there are many permissions assigned to the fixed server roles, however, when I look at the server permissions assigned to a server login, not all of the permissions listed as a result of sp_srvrolepermission are tied to a server login.

 


Thursday, June 02, 2011 - 10:39:10 AM - Sam Schafer Read The Tip

Thank you for the details. BOL lists the permissions but does not explain them. Most you can glean from the name, some not quite so clear.


Thursday, July 08, 2010 - 3:18:44 AM - tinof Read The Tip

Please allow a question:

You wrote to 'ALTER ANY LOGIN" 

... 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.

I can't see this in my enviroment (SQL Server 2005):

I can drop a login assigned to the sysadmin role and with CONTROL SERVER permission even if i am logged in as a user who does'nt have the CONTROL SERVER permission. What is wrong?

I would need 'Master -User', who administrate other logins, but they better should'nt be able to delete i.e. the sa - user ;-) .

Thank's

Tino


Tuesday, March 24, 2009 - 3:37:57 AM - @tif Read The Tip

 I would just like to add that list of all server level permissions may be generated as following if required.

SELECT permission_name
FROM fn_my_permissions(null, 'server')
order by 1
GO




 
Sponsor Information