after studying how NAV adds a new user and his role to it’s database using CSIDE client i would like to do it using SQL. I found out that when i use CSIDE to add user it preforms an insert :
INSERT INTO "NavDB"."dbo"."User" WITH (REPEATABLEREAD) ("User ID","Password","Name","Expiration Date") VALUES 'User_1','','John','1753-01-01 00:00:00'
and then after adding all users and their roles (Tools → Security → Database Logins) i must call Synchronize all logins from CSIDE client. I found out that i can use a SYNCHORNIZEALLLOGINS function to achieve this trough SQL which documentation is here (msdn.microsoft.com/…/ee414198.aspx)..)
So i’m asking if you can provide me code how to do this?
Thanks for pointing it out but I’ve seen this already. It’s for window logins and I need to add Database logins. Basicly i want to know in which tables i need to insert user info to achieve this.
you need to insert into 2000000002 - User table for database logins
You have the insert statement.
You have the command to synch the logins.
You have a tool like Client Monitor to watch the SQL that gets executed when you create a user and create the roles.
What exactly do you need? The Member Of table, right after the User table in Object Designer, stores the roles for a database user. You should be able to take the concepts in Gaspodes blog and apply them.
I used SQL profiler to see what Nav is doing “behind the covers” while i’m clicking in CSIDE client. I was just wanted to be sure and get advice from pros before i make adjustments i need
Can you tell me how to execute SYNCHRONIZEALLLOGINS functions with SQL. I keep getting error: ‘SYNCHRONIZEALLLOGINS’ is not a recognized built-in function name.
Thanks in advance