But when you migrate to SQL Server 2005, The same roles assigned to users does not allow them to change to their own password. When your users tries to change it, they will see the following error -
"The following SQL Server error(s) occurred:
15151,"42000",[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot alter the login 'nisheel', because it does not exist or you do not have permission.
SQL:{CALL [sp_password](?, ?)}"
When you change your password from Navision client, the stored procedue 'sp_password' is called and the necessary permission to execute this stored procedure was included in 'Public' role of SQL Server 2000.
But the same permission is no longer associated with 'Public' role in SQL Server 2005. And that's why you will have the problem in changing the password. And you are taking a dangerous decision of assigning 'dbowner' role to your users.
To resolve it without assigning the 'dbowner' role, you need to explecitely assign a specific permission to your 'Public' Role (the permission having execute power of Sp_password). Follow the instruction below
1. Follow the standard procedure of creating a new users and mapping them to a your Navision database. While User Mapping, assign the normal roles Public, db_datareader, db_datawriter.
2. Now, assign the permission 'Alter any Login' to your 'Public' role. Select the Server and open its property window.
Select 'Permission' page in left hand pane and select 'Public' role in the right hand pane. Scroll down and find the 'Alter Any Login' permission. Grant this permission to 'Public' Role.
Do remember, you need to be System Administrator to do the above setup.
Now try and see whether your users are able to change their own password or not. They will be able to do that.
This may not be the best possible solution and you are welcome to send your suggestion or any other solution to it.
Do send me your comments and suggestions.
![Reblog this post [with Zemanta]](http://img.zemanta.com/reblog_e.png?x-id=fa883de3-b5ec-45cc-bab0-9bc394cdc1d2)
Posts
6 comments:
to over come that problem we've mainly concentrate on the differences between sql2000 and sql 2005
1)One other possiblity is that SQL 2005 passwords are case sensative. In 2000 by default it was case-insensative. If your users are used to case-insensative they may be entering the passwords differently each time. That would account for them being able to log in sometimes and not others
2)The default database was master on 2000, and still is on 2005. The application login takes care of connecting to the correct database. It is the application that prompts for the user ID and password.
If this was the problem, then it should affect everybody. But the problem seems to be random.
3)..or - the passwords don't meet the requirements and you have the "enforce password policy" turned on....
4)Have you audited your SQL Profiler or a server side trace looking for Audit Failures?
to over come this
1)create new logins in sql server ->security -->logins
2)create a new username and enforce password policy on and in usermapping select the database.thats it press ok
3)then go to navision and create the same user with specified roles and synchronize the login.
when we tried it givies no error.
Wow...as Nisheel Poddar I would like to hear from the expert's mouth how I solved the problem :)
Hi Nisheel,
I am new Navision worker and i am setting up a client server environment with SQL server option . I have already what ever you have done i am able to establish a client-server setup. But still i have aproblem when i am using navision, the user who has these priviliges or any other user who logs in navision is not able to create any table or records in navision. I am currently using nav5 and SQL erpress 5.
Worked like a charm, thanks.
Brilliant, worked perfectly, thank you!
great, thanks a lot :-)
this helped me with nervous customer :-)
Post a Comment