locked
Locking Down SQL 2005 RRS feed

  • Question

  • I'm not sure what the best way is to lock down sql server 2005. I added a login group that maps to an AD group. This group is only mapped to the public role and the problem is that anyone in this group can manage the server my right-clicking and selecting properties. My understanding that this is allowed by the control server permissions but removing that doesn't allow them to logon to the server anymore. Is there a way to allow people to connect to the server but not manage it?

    Thanks!
    Wednesday, February 17, 2010 1:05 AM

Answers

  • By default when you give a login only the public fixed server role, it has only the server permission of "Connect SQL". It should not have the CONTROL SERVER permission which is actually a sysadmin. Since you can change the server settings, it is probably that the logins belong to an existing built-in admin group.  Besides what Lekss mentioned, I recommend that you run the following query to check the details:
    1. To check your principal's id, run
    SELECT * FROM sys.server_principals

    2. To check the detailed server permission, run
    SELECT * FROM sys.server_permissions

    Pay attention to the following columns in the output: class_desc, grantee_principal_id, permission_name and state. 
    http://msdn.microsoft.com/en-us/library/ms186260.aspx

    If any of the logins have the CONTROL SERVER permission, you can issue REVOKE command as following to revoke the permission:
    REVOKE CONTROL SERVER FROM login_principal_name

    If after revoke the CONTROL SERVER permission, you find that you could not log on your SQL SERVER instance, you can use GRANT to grant the CONNECT SQL to it:
    GRANT CONNECT SQL to login_principal_name

    And make sure that it only has CONNECT SQL server permission in sys.server_permissions. Please aware that do not issue DENY CONTROL SERVER to your login which will cause the login failed to connect to your SQL Server instance. If you already issued a DENY statement, issue REVOKE to revoke it.

    For more information, You can refer to:
    Deny Server Permissions
    http://msdn.microsoft.com/en-us/library/ms182763.aspx
    GRANT Server Permissions
    http://msdn.microsoft.com/en-us/library/ms186717.aspx
    REVOKE Server Permissions
    http://technet.microsoft.com/en-us/library/ms186308.aspx


    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Thursday, February 18, 2010 4:02 AM

All replies

  •  

    If you are very sure that they are only in the public role and trying to change the server properites - this is not possible and that

    will give an error like the below.

    User does not have permission to perform this action. (Microsoft SQL Server, Error: 15247)

     

    You have talked about managing server properties with public role , can you elaborate on the settings that they were able to modify.


    Thanks, Leks
    Wednesday, February 17, 2010 1:36 AM
  • It almost seems they somehow have sysadmin capabilities. I have tested it with a user that is only a domain user and I can change server properties like max memory of the sql server. The user I was testing is a local admin. That shouldn't translate at all to the database server should it?
    Wednesday, February 17, 2010 2:31 AM
  • In general , domain administrators and local admins on a machine can gain SYSADMIN access to the SQL server if builtin admin

    account is not removed from SQL logins( Till SQL 2005). This is why it is recommended to run SQL services under a domain account and revoke access for builtin account.

    This behaviour has changed from SQL 2008.


    Thanks, Leks
    Wednesday, February 17, 2010 2:55 AM
  • Yeah I saw that BUILTIN\Administrator login was enabled on the server but removed the login. But it seems the account could still gain sysadmin access.
    Wednesday, February 17, 2010 4:43 AM
  • Do not remove builtinadmin account if your SQL services are running under local system account .
    Thanks, Leks
    Wednesday, February 17, 2010 4:53 AM
  • Hi,

    It is really important to remove builtin administrator group, but before doing so please add a user which has SA rights.  Refer this article http://www.mssqltips.com/tip.asp?tip=1017, http://www.sqlservercentral.com/articles/Security/removingthebuiltinadministratorssomepitfallstoavoi/1661/. There are few more points to be taken care while implementing sql server security refer an article here http://www.sql-server-citation.com/2009/05/how-to-secure-sql-server-sql-server.html
    Hemantgiri S. Goswami | http://www.sql-server-citation.com/ | http://www.surat-user-group.org/ -- Surat SQL Server User Group
    Wednesday, February 17, 2010 7:05 AM
  • So removing this BuiltIn\Administrator group should then not let someone that is a local admin be a sysadmin right? Also does this affect sql logins or just windows authentication logins? I have another login that is limited but it can also edit server settings unfortunately. Is there a way to find out which users have sysadmin rights on the server?

    Thanks!
    Wednesday, February 17, 2010 3:40 PM
  •  так выглядит нажатая клавиша Е на английском языке €, че делать?
    Wednesday, February 17, 2010 4:01 PM
  • Builtin admin account affects only windows authenticated logins ( local window accounts and domain account) . To see the logins those have been granted with sysadmin access , use the following query

    select name , sysadmin from sys.syslogins where sysadmin = 1

    Remember the above script will only show the logins that have been added to SQL server logins .
    Thanks, Leks
    Wednesday, February 17, 2010 6:35 PM
  • By default when you give a login only the public fixed server role, it has only the server permission of "Connect SQL". It should not have the CONTROL SERVER permission which is actually a sysadmin. Since you can change the server settings, it is probably that the logins belong to an existing built-in admin group.  Besides what Lekss mentioned, I recommend that you run the following query to check the details:
    1. To check your principal's id, run
    SELECT * FROM sys.server_principals

    2. To check the detailed server permission, run
    SELECT * FROM sys.server_permissions

    Pay attention to the following columns in the output: class_desc, grantee_principal_id, permission_name and state. 
    http://msdn.microsoft.com/en-us/library/ms186260.aspx

    If any of the logins have the CONTROL SERVER permission, you can issue REVOKE command as following to revoke the permission:
    REVOKE CONTROL SERVER FROM login_principal_name

    If after revoke the CONTROL SERVER permission, you find that you could not log on your SQL SERVER instance, you can use GRANT to grant the CONNECT SQL to it:
    GRANT CONNECT SQL to login_principal_name

    And make sure that it only has CONNECT SQL server permission in sys.server_permissions. Please aware that do not issue DENY CONTROL SERVER to your login which will cause the login failed to connect to your SQL Server instance. If you already issued a DENY statement, issue REVOKE to revoke it.

    For more information, You can refer to:
    Deny Server Permissions
    http://msdn.microsoft.com/en-us/library/ms182763.aspx
    GRANT Server Permissions
    http://msdn.microsoft.com/en-us/library/ms186717.aspx
    REVOKE Server Permissions
    http://technet.microsoft.com/en-us/library/ms186308.aspx


    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Thursday, February 18, 2010 4:02 AM