locked
SSMS login issue RRS feed

  • Question

  • Dear Experts,

    We have a server. I can login to that server and when I click on SSMS it is automatically taking my windows login credentials and logging into SSMS. I want another windows login to be able to login to SSMS on that server, and he is not an admin of that server


    Kiran

    Wednesday, November 6, 2019 1:15 PM

All replies

  • You do not login to SSMS, you logon with SSMS to a SQL Server; create a Login for that Windows user in that SQL Server: Create a Login

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, November 6, 2019 1:20 PM
  • That windows user already has a login.

    Kiran

    Wednesday, November 6, 2019 1:54 PM
  • As Olaf pointed you need to create a login for that user https://www.tutorialgateway.org/create-windows-login-in-sql-server/

    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

    Wednesday, November 6, 2019 1:59 PM
  • Hello,

    To use the windows credentials that user has to be logged in and launch SSMS. 

    Is it a domain user or local account?

    Does the user have login permissions to SQL? 

    Wednesday, November 6, 2019 2:03 PM
  • Since the user is not allowed to logon locally, then if you want to use windows integrated authentication, make sure you have added the needed SPNs on the DB engine account and the user can install SSMS on his machine (same domain) and login to the instance. He will naturally be required to have permissions to do so and that is granted to the login in SQL, which was mentioned several times in this thread. The login must also be for this windows identity of the user.

    [SharePoint lurker]

    Wednesday, November 6, 2019 2:10 PM
  • It is a domain account. I don't think he has login permissions to SQL 

    Kiran

    Wednesday, November 6, 2019 3:14 PM
  • When I login to the server and launch SSMS, it is automatically taking only my windows credentials. I want SSMS to take other user credentials when he is launching SSMS.

    Kiran

    Wednesday, November 6, 2019 3:21 PM
  • when he is launching SSMS
    When he is logged on to Windows and starts SSMS, then his Windows account is used and that one requires logon permissions to SQL Server.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, November 6, 2019 3:26 PM
  • If SSMS is launched on your machine the default authentication is 'Windows Authentication'. SQL Server validates the account name and password using the Windows principal token. No password is required here.

    Yes, it takes your credentials automatically, unless you select SQL authentication (input id/password).

    What exactly you are trying to achieve by doing this - "I want SSMS to take other user credentials when he is launching SSMS." to help you further.


    Best Regards. Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    Wednesday, November 6, 2019 4:42 PM
  • When the user is logged in and launches SSMS it will use his Windows Credentials to login. However if the user does not have permissions in SQL to login then it will reject him.

    Launch SSMS and add the windows user you want to give access to with the relevant permissions over Databases. Make sure on the final tab of adding a user the user has permission to login to SQL.

    Wednesday, November 6, 2019 4:56 PM
  • I want all windows logins to be able to connect to SQL instance, not just me.

    Kiran

    Wednesday, November 6, 2019 5:04 PM
  • Hi juniorkiran,

     

    When using Windows Authentication, it will use your current Windows login account. And  you cannot change it unless you use different account to login the windows. You should give the domain account  access to the sql serverf and login the windows with it.

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, November 7, 2019 5:58 AM
  • If you have Active Directory, You can Create a AD Group to have all the Windows Logins. If not, you have to Add the Windows Login Individually to SQL Server to be able to Connect.

    Below is to Add all the Users Part of Local Windows Administrator Group and Local Users Group to SQL Server

    USE [master]
    GO
    CREATE LOGIN [BUILTIN\Administrators] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
    GO

    USE [master]
    GO
    CREATE LOGIN [BUILTIN\users] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
    GO

    Once Add the Local Users or Administrators Groups to SQL Server, Any User added to the local groups can access sql server based on the permissions given.

    Regards,

    Vishnu

    Thursday, November 7, 2019 6:48 AM
  • We have a SQL server instance. When my colleague is trying to launch SSMS , it is by default taking my credentials, and not my colleague's credentials. 

    Kiran

    Thursday, November 7, 2019 2:56 PM
  • When my colleague is trying to launch SSMS -> Where is he doing this?

    Best Regards. Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    Thursday, November 7, 2019 5:40 PM
  • When my colleague is trying to launch SSMS , it is by default taking my credentials, and not my colleague's credentials

    Impossible, SSMS uses always the credential of the current logged on Windows account; SQL Server don't support impersonation of a different Windows account.

    If really your Windows account is used, then because the user logon to Windows with your Windows account.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Thursday, November 7, 2019 7:27 PM
  • Please let us know how the user can able to login in SSMS using Windows authentication if the user does not have the OS user created in the server.

     


    Kiran

    Friday, November 8, 2019 12:50 PM
  • Create a SQL Login with password

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, November 8, 2019 1:40 PM
  • Hi Olaf,

    So, the user should have OS user created in the server, correct?


    Kiran

    Friday, November 8, 2019 2:09 PM
  • it should be a SQL login, not a windows login. 

    create login <login-name> with password = '<password>';
    go
    create user <user-name> for login <login-name>;
    go
    use <database-name>
    go
    grant <permission-name> on <object-name> to <user-name>
    go

    or if you want a windows login and this login authenticates the user when he starts the SSMS and connect to server

    CREATE LOGIN [Domain_Name\Login_Name] FROM WINDOWS
    GO


    Best Regards. Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it.

    Monday, November 11, 2019 3:29 PM