none
Login sql server by group account

    Question

  • On windows server 2012 R2, I create 5 accounts with password from administrator account. After that, I go to Control Panel\All Control Panel Items\Administrative Tools\Computer Management\Local Users and Groups\Group and then I create a group "RE". I add all 5 accounts to RE group.

    After finished, I open sql server studio management and create window authentication account for group. The username is [ServerName]\RE.

    Then I logout and use 1 of 5 accounts to login windows.

    The problem is I cannot login sql server studio management by windows authentication mode.

    So why? I create group account in sql server and all account added in that group.

    Tuesday, December 20, 2016 5:49 AM

Answers

  • Hi Ali Le,

    This should work fine. I just tried in my lab and no issues with this.

    Are you sure you logged in back to server using Local account and not domain account?

    What are the permission level of those 3 or 4 windows users on the server machine?

    Also, What permissions had you granted to the windows group which you added as login in SQL server?

    Can paste screen shot of Error message?

    Thanks,


    Kindly mark the reply as answer if they help


    • Edited by Sunil Gure Tuesday, December 20, 2016 11:43 AM edit
    • Marked as answer by Ai Le Tuesday, December 20, 2016 12:31 PM
    Tuesday, December 20, 2016 11:39 AM
  • FYI. Screen shot of the same in my lap env..


    Kindly mark the reply as answer if they help

    • Marked as answer by Ai Le Tuesday, December 20, 2016 12:31 PM
    Tuesday, December 20, 2016 11:51 AM

All replies

  • What error are you getting?

    https://www.mssqltips.com/sqlservertip/1831/using-windows-groups-for-sql-server-logins-as-a-best-practice/


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    • Marked as answer by Ai Le Tuesday, December 20, 2016 8:34 AM
    • Unmarked as answer by Ai Le Tuesday, December 20, 2016 8:34 AM
    Tuesday, December 20, 2016 6:29 AM
  • Example:

    On the same server computer

    From administrator in windows server, I have 5 accounts on windows server:
    - account1

    - account2

    - account3

    - account4

    - account5

    I add 5 accounts to 1 group "RE".

    Go to SQL Server Management Studio -> create sql account [ServerName]\RE, Windows Authentication mode

    Log out administrator windows account

    Log in account1 to windows server

    Go to SQL server Management Studio -> choose server [ServerName] -> Windows Authentication mode

    -> I got error. System show account not exist.

    Tuesday, December 20, 2016 7:04 AM
  • Hi Ai,

    try with GUI ,group should present in Active directory.

    USE [master]
    GO
    CREATE LOGIN [MC0XENTC\WSS_WPG] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
    GO
    
    2.
    ALTER SERVER ROLE [sysadmin] ADD MEMBER [MC0XENTC\WSS_WPG]
    GO
    

    share error log.


    Please Mark it as Answered if it answered your question OR mark it as Helpful if it help you to solve your problem.

    Tuesday, December 20, 2016 8:24 AM
  • I did it. But don't work.

    Message is as below:

    Create failed for Login '[SeverName]\Users'.  (Microsoft.SqlServer.Smo)

     

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.6020.0+((SQL11_PCU_Main).151020-1526+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Login&LinkId=20476

     

    ------------------------------

    ADDITIONAL INFORMATION:

     

    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

     

    ------------------------------

     

    Windows NT user or group '[SeverName]\Users' not found. Check the name again. (Microsoft SQL Server, Error: 15401)

     

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=11.00.6248&EvtSrc=MSSQLServer&EvtID=15401&LinkId=20476

    Tuesday, December 20, 2016 9:31 AM
  • Hi Ai,

    use [SeverName\Users]  -- share login screenshot and error screenshot

    syntax :- SELECT name FROM syslogins WHERE sid = SUSER_SID ('SeverName\Users'')

    query :-SELECT name ,createdate,updatedate FROM syslogins WHERE sid = SUSER_SID ('Hp-pc\Hp')


    Please Mark it as Answered if it answered your question OR mark it as Helpful if it help you to solve your problem.


    • Edited by Av111 Tuesday, December 20, 2016 10:53 AM screenshot
    Tuesday, December 20, 2016 10:51 AM
  • Hi Ali Le,

    This should work fine. I just tried in my lab and no issues with this.

    Are you sure you logged in back to server using Local account and not domain account?

    What are the permission level of those 3 or 4 windows users on the server machine?

    Also, What permissions had you granted to the windows group which you added as login in SQL server?

    Can paste screen shot of Error message?

    Thanks,


    Kindly mark the reply as answer if they help


    • Edited by Sunil Gure Tuesday, December 20, 2016 11:43 AM edit
    • Marked as answer by Ai Le Tuesday, December 20, 2016 12:31 PM
    Tuesday, December 20, 2016 11:39 AM
  • FYI. Screen shot of the same in my lap env..


    Kindly mark the reply as answer if they help

    • Marked as answer by Ai Le Tuesday, December 20, 2016 12:31 PM
    Tuesday, December 20, 2016 11:51 AM
  • Hi Ai,

    use [SeverName\Users]  -- share login screenshot and error screenshot

    syntax :- SELECT name FROM syslogins WHERE sid = SUSER_SID ('SeverName\Users'')

    query :-SELECT name ,createdate,updatedate FROM syslogins WHERE sid = SUSER_SID ('Hp-pc\Hp')



    Please Mark it as Answered if it answered your question OR mark it as Helpful if it help you to solve your problem.


    Sorry I cannot share screenshot because forum doesnot allow me to post comment with image.

    I run query and got answer

    [ServerName]\RE_Users    2016-12-19 03:11:34.667    2016-12-19 06:38:11.793

    Tuesday, December 20, 2016 12:06 PM