Post reply

Disabling windows authentication for a database

  • March 30, 2011 at 2:34 pm

    #235971

    Hello Experts.

    Is there a way to disable windows authentication for a specific database? I mean deny access to a specific database when logging to Sql Server using windows authentication. I want a database to be accessible either by sa or its owner user. Other users have already been denied to access the database. This is consideration for software licensing as I am storing information about trial version in the database and do not announce the password of database owner.

    Many Thanks.

  • March 31, 2011 at 1:28 am

    #1305819

    Don't create database users for windows logins, that should be enough.

    -- Gianluca Sartori

  • March 31, 2011 at 1:41 am

    #1305825

    Hi.

    When you create a database, by default it grants access to windows logins. How do I remove privileges for windows logins?

    Thanks.

  • March 31, 2011 at 1:44 am

    #1305828

    I don't think so. By default, members of the local administrators group are granted the sysadmin role, but it's quite a different thing.

    -- Gianluca Sartori

  • March 31, 2011 at 1:53 am

    #1305832

    Hi.

    So if the user's windows account is an administrator account, he/she can simply access the database using windows authentication. Right?

  • March 31, 2011 at 2:04 am

    #1305836

    Exactly.

    -- Gianluca Sartori

  • March 31, 2011 at 2:38 am

    #1305846

    So, how do I avoid this?

  • March 31, 2011 at 2:39 am

    #1305847

    It's good practice to remove the BUILTIN\Administrators login and just grant access to those who need it. Be very careful when you do this on a clustered instance, though - if you remove access to the cluster service account then SQL Server won't start.

    John

  • March 31, 2011 at 2:49 am

    #1305851

    Dear, isn't possible to just deny access for BUILTIN\Administrators to the database instead of making changes to Sql Server nature?

    Thanks.

  • March 31, 2011 at 2:53 am

    #1305853

    I'm afraid it won't work. At least it would be useless, because a sysadmin could always re-enable access for his account.

    -- Gianluca Sartori

  • March 31, 2011 at 3:01 am

    #1305856

    OK. Many thanks for your discussion.

  • March 31, 2011 at 3:04 am

    #1305859

    Besides, as far as I know, sysadmin logins have full access to the server, even overriding any DENYs that may be in place. This applies even if you drop BUILTIN\Administrators but make one or more Windows logins members of the sysadmin role.

    John

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply