19 Responses to “SQL Server Coding Tips”

  1. Victoria –
    Ever have an issue where a GP user’s password was changed in SQL Server Management Studio and then they couldn’t get into GP with that password. Then when you change the password from inside of GP they can’t access Sql Server management studio. To access SSMS we are using sql authentication

    Like

    • Cherie,

      This is by design. When a user password is set up in the Dynamics GP application, the password is encrypted and can then only be used with the Dynamics GP application, which will be able to decrypt it. You will not be able to use that password anywhere else, as nothing else will have the decryption key. In addition, because of how Dynamics GP handles permissions, if you were able to use the same SQL user/password in SSMS, the user would have access to all database objects, which you may not always want. I would recommend setting up a different user ID directly in SQL for users that need to access to use SSMS. Typically I see this done using the active directory login.

      -Victoria

      Like

      • Thanks Victoria. That’s what I thought. I’ve just started at this company and everywhere else I’ve worked when accessing SSMS I’ve used windows authentication.

        Like

      • Thanks for the help. We just created a new userid in sql like you suggested.

        Like

  2. Splendid work by victoria, Awesome, hats off to you

    Like

  3. Hi Victoria

    Awesome, thanks u so much for ur reply.

    Please mark me for the future questions.

    Many thanks indeed…….

    Like

  4. Hi Victoria

    How to reset the password in GP 10.0 if the user is not logged off properly. When the administrator reset the password, message appears like “password has not been changed. The User is logged in”.

    Do I have to reset password through Backend ‘ SQL Server’ and how it should be done. Please Explain……

    Many Thanks

    Like

    • Sharmila,

      I would recommend getting all users out of GP and running the following in SQL Server Management Studio:

      Delete DYNAMICS..SY00800
      Delete DYNAMICS..SY00801
      Delete DYNAMICS..ACTIVITY
      Delete TEMPDB..DEX_LOCK
      Delete TEMPDB..DEX_SESSION

      -Victoria

      Like

      • I was trying to upgrade our GP development server to SP2 and when I ran Utilities, it told me that someone was logged in, However, I already ran the SP2 install and couldn’t log in to GP to kick them out. I found several solutions to remove a user stuck in GP, including a couple of KB from Microsoft, but yours is the one that did the trick.

        Like

Trackbacks/Pingbacks

  1. SQL stored procedure to search an entire database : Interesting Findings & Knowledge Sharing - July 22, 2013

    […] You can see links to other helpful SQL Server tips on my SQL Server Coding Tips page. […]

    Like

  2. SQL stored procedure to search an entire database - Victoria Yudin - Microsoft Dynamics GP - Microsoft Dynamics Community - July 22, 2013

    […] You can see links to other helpful SQL Server tips on my SQL Server Coding Tips page. […]

    Like

  3. SQL stored procedure to search an entire database | Victoria Yudin - July 22, 2013

    […] SQL Server Coding Tips […]

    Like

  4. Changing part of a string in SQL Server using REPLACE : Interesting Findings & Knowledge Sharing - March 11, 2013

    […] To see more tips and tricks for GP Reports Viewer, SQL , SSRS and Crystal Reports, take a look at the GP Reports Viewer newsletters. There are also more SQL Server coding tips on this blog. […]

    Like

  5. Changing part of a string in SQL Server using REPLACE | Victoria Yudin - March 11, 2013

    […] SQL Server Coding Tips […]

    Like

  6. Concatenating strings in SQL Server - Victoria Yudin - GP Technical Blogs - Microsoft Dynamics Community - May 21, 2012

    […] more tips like this, take a look at my SQL Server Coding Tips page. To receive new tips as soon as they are published, sign up for the GP Reports Viewer […]

    Like

  7. Concatenating strings in SQL Server – 5/21, Victoria Yudin | Partner Compete - May 21, 2012

    […] more tips like this, take a look at my SQL Server Coding Tips page. To receive new tips as soon as they are published, sign up for the GP Reports Viewer […]

    Like

  8. Concatenating strings in SQL Server | Victoria Yudin - May 21, 2012

    […] SQL Server Coding Tips […]

    Like

  9. Getting the name of a month from the month number in SQL Server - Victoria Yudin - GP Technical Blogs - Microsoft Dynamics Community - May 23, 2011

    […] tips like this can be found on my SQL Server Coding Tips page. For more Dynamics GP code, check out my GP Reports page. Filed under: GP Reports code, SQL […]

    Like

  10. Getting the name of a month from the month number in SQL Server | Victoria Yudin - May 23, 2011

    […] SQL Server Coding Tips […]

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,798 other followers

%d bloggers like this: