SQL Server Performance

Alternative of BUILTINAdministrators Logins

Discussion in 'General DBA Questions' started by sonnysingh, Feb 27, 2008.

  1. sonnysingh Member

    Hi All
    I have removed BUILTINAdministrators logins to stop access Windows users. Now, I want to connect to SQL Server with windows logins that of course not allowed. What should I need to do to connect SQL Server through my Windows logins? I have gone through the this article but not any help on this..
    SQL Server 2000 Security - Part 2 (Authentication) - (http://www.databasejournal.com/features/mssql/article.php/3341651 )
    Thanks in Advance....

  2. Greg Larsen New Member

    I'm not sure I understand what you are trying to do. Removing the BUILTINAdministrators login stops logins that have administrator permissions on the SQL Server machine from logging in to SQL Server. Hopefully prior to removing the BUILTINAdministrators login in SQL Server you gave another Windows Account, or SQL Account (provided you are running in mix mode) "sysadmin" permission. If you are running in mix mode then the
    "sa" login will have "sysadmin" permissions. If so then logon using one of the account that has "sysadmin" permissions and then you can grant access to those windows logins that need access you your SQL Server machine.
  3. sonnysingh Member

    Yes we are running mixed mode authentication. We also have "sa" login and another account that have sysadmin rights on SQL Server. Like you mentioned in your reply that "Hopefully prior to removing the BUILTINAdministrators login in SQL Server you gave another Windows Account".
    I want know that how should do create another windows account and what are steps i need to follow to create successful windows account to connect SQL Server?
    Thanks in Advance
  4. Adriaan New Member

    You can add an individual Windows account (local or domain) the same way as a SQL login: go to Security > Logins, and add the Windows account. Under Server Roles, tick System Administrator.
  5. sonnysingh Member

    I have tried to add my domain login user for Windows Account as you have suggested Adriaan. This user also member of Administrator groups on the Server. But unfortunately, denied access when I try connect to the SQL Server.
    Error: Login Failed for user 'delhi 45632'
    Please help what I am missing...
    Thanks in Advance

  6. Adriaan New Member

    I assume you have set up the password for the 'sa' login? Login as 'sa', and check if the BUILTINAdministrators have been denied db access, rather than just having been removed from the list of system administrators.
    Denying a permission to a Windows group will overrule any attempt to grant that same permission to a member of that Windows group.
  7. sonnysingh Member

    Yes, we have 'sa' login. BUILTINAdministrators has been removed and that's why I want to create my Windows Login as member of sysadmin role. Like i have mentioned that I done it but could not login...do i missing something ? please help
    Thanks!!!

  8. satya Moderator

    If you are trying to add a windows user to sql server you need to add as referred above with Enterprise Manager or using TSQL:
    sp_grantlogin [@loginame=] 'login'
    Then set appropriate permissions using SP_GRANTDBACCESS and for such information referring to books online will get you code examples too.
  9. sonnysingh Member

    I have tried the way you have suggested as
    exec sp_grantlogin 'delhiS12345'
    exec sp_grantdbaccess 'delhiS12345','delhi'
    But again same ERROR: Login Failed for user 'delhiS12345'
    do i missing something again?
    Thanks !!!
  10. satya Moderator

  11. sonnysingh Member

    I have looked at referred link and there are not same reasons for Login failed for user that I am having. Let me describe the present situation
    • create windows login as login for SQL Server
    • grant login rights
    • grant db access
    • no BuiltIN/Administrator
    Error : Login Failed for User: 'delhiS12345'
    Thanks !!!
  12. Adriaan New Member

    Is the instance of SQL Server registered in the Delhi domain?
    Did you check if this user, or any Windows group to which this user belongs, is denied db access?
  13. johnson_ef Member

    HI,
    As Adriaan suspected, this could be the problem of Trusted SQL Connection issue.
    Try to map the Windows login from Wizard, this will help you to clear the Trusted Connection has done properly. while mapping through Wizard, the 'domainlogin' will resolve its own. then you can give the access to the respective database with specific roles.
    Regards
    -Johnson
  14. sonnysingh Member

    Hi
    I Have used login wizard and even use the following code to create new windows domain login. But again got same error.

    use MASTER
    go
    exec sp_revokedbaccess 'S12345'
    exec sp_revokelogin 'delhiS12345'
    go

    use VC_DataSource_New
    go
    exec sp_revokedbaccess 'S12345'
    exec sp_revokelogin 'delhiS12345'
    exec sp_grantlogin 'delhiS12345'
    exec sp_defaultdb 'delhiS12345','Pubs'
    exec sp_grantdbaccess 'delhiS12345', 'S12345'
    go
    Please help..
    Thanks in Advance.....
  15. johnson_ef Member

    HI Sonny Singh,
    I got some clue on this after some research on this issue, Please try this and let me know.
    I hope the Security Mode of your server is 'Windows Authentication', Please change it to Mix mode (SQL Server and Windows Authentication).
    Now this should work. Try this and let me know.
    Regards
    -Johnson
  16. sonnysingh Member

    Thanks.....
    I have checked and the security mode is Mixed Mode. I have also checked that this server exist in Delhi domain. But till same error.
    Please help...
  17. ghemant Moderator

  18. suniljk7 New Member

    Dear sonnysingh,
    I think your problem is like this: you denied access of sql server for BUILTINAdministrators user. This will deny access of all members, in this group to sql server. I think your 'delhi' user is also a member of this BUILTINAdministrators group thatz why ur are not getting access even though you given access through sql server. so what i suggest is, you to create a separte user which is not member of admin group and give access to sql and then try. hope this will work.
    Experts please correct me is am wrong.
    Thanks
    John

  19. sonnysingh Member

    Thanks a lot... I have followed the suggestion from you and Hemant...But same error ..even tried login wizard as well. Nevertheless, I have tried with SQL 2005 and it is worked.. What I have done is...
    • Add new login with same domain user (delhiS12345)
    • Deny access of Builtin/Administrators under Status -- Settings -- Permissions to connect to Database Engine
    • But on the same page keep Enabled on login -- Enabled
    and it is working fine... Now if someone with Windows Login try to connect Server.. cannot connect as it only Windows login rather part of SQL Login.
    So question is can we have similiar option like we have in SQL 2005 as mentioned above OR command that solve same situation in SQL 2000? We can Deny permissions to connect database but Enabled Login for Windows account that added as SQL Login.
    Thanks a lot again....

Share This Page