enumusergroups AND enumusersids?

DenSter

x64, SQL Server 2005, NAV 4.0 SP2 update 3, installed esp xp_ndo_enumusergroups, pointed it to the x64 version of the dll. When creating a new database, I get an error saying can't find xp_ndo_enumusersids.

Now I thought that the sids one is for 3.7 and earlier, and that all we'd need is the usergroups one. Do I need to create both extended SP's? Do I point both of them to the same dll or do I point the sids one to the regular xp_ndo one?

ara3n

Hello Daniel
I instaled the same thing with same configuration a week ago. I only created xp_ndo_enumusergroups.

Through the following process.

added the xp_ndo_x64.dll to the binn folder. ran the following code.

USE master
EXEC sp_addextendedproc xp_ndo_enumusergroups, 'C:\Program Files\Microsoft SQL Server\MSSQL\Binn\xp_ndo_x64.dll'
GO

GRANT EXECUTE
ON [xp_ndo_enumusergroups]
TO PUBLIC
GO

DenSter

xp_ndo.dll is not the 64bit one though, and I believe that path you mention is the SQL Server 2000 path, this customer is on SQL Server 2005. I did create enumusergroups, the system is screaming at me because it can't find enumusersids and that is what is confusing me because I thought that was used only for 3.7 and lower.

ara3n

Sorry it was xp_ndo_x64.dll. I changed it while you were reading it. Yes they are on SQL 2005 64 bit version.

DenSter

Do I need to restart SQL Server after creating the ESP's?

ara3n

I'm trying to remember if I did that. You should try it, it wouldn't hurt.

DenSter

Alright thanks, I'll have them try that

ara3n

Ok I think I've found the issue. I had update 2, not the one from MS.

Here is the white paper on the new security feature.

To change the security model used in the database, you must be:
• A member of the sysadmin server role on SQL Server or be a member of the db_owner database role for the database in question.
• Assigned the SUPER role in Navision.

Furthermore, if you want to change security models, you must ensure that both of the extended stored procedures that come with Navision have been added to the instance of SQL Server that you are using. These extended stored procedures are called:
• xp_ndo_enumusergroups
• xp_ndo_enumusersids

These extended stored procedures are part of the xp_ndo.dll that comes on the Navision product CD. For more information about installing the extended stored procedures, see the section Attaching xp_ndo to SQL Server or read the Readme.txt file that is stored with the dll on the product CD.

So yes you have to create both since you can use simple or enhanced security. and you need both for SP3 and update 3 in sp2

DenSter

That makes sense. I'll have them add the other one as well, and point to the x64 dll. Thanks for the follow up, I appreciate it.

ara3n

No problem. I hope they update the readme file on the sp3 install CD.

DenSter

Yes that would be a good idea

kine

enumusersids is used with simple security model (3.70 and older and 4.00SP2Update 3)

enumusersgroups is used with complex security model (4.00+)

DenSter

Right, and it would have been nice for MSFT to tell us that with the updated executables, both of those security models are available again, so in a 4.0 SQL Server 2005 environment you have to create an enumusersids extended stored procedure. This would have taken about 25 seconds to do, instead of hours of research, trial and error.

ara3n

I looked at SP3 readme.txt for SQL and they did not update it for SP3.

kriki

[Topic moved from 'NAV/Navision' forum to 'SQL General' forum]
[Attribute changed from <Solved> to <Good Posting>. Reason:the script to install the dll]