Re: Synchronizing database logins with Sql Server

swimmer (swimmer@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
1.)Yes. The Synchronization is performed in the Navision Database.

2.) Error Message:
The Microsoft Business - Solutions Navision and the SQL Server
Security Systems have not been synchronized successfully. The SQL Server
login does not exist ont he Fourtest Server.

As I said that I don't know what Navision is, so I will have make
wild guesses. I would assume that Navision maintains its own user
table, and when you synchronize, you synchornize that user table with
the database users and their logins.

3.) The database was restored from another server. The users were
added to the SQL Server first and then the database was restored. This
function works on the original server but not on the new server.

When you restore a database from one server to another, you lose the
connection between the database users and the logins. Say that on the
source server there is a login Alfons and in the Navision database
there is a user with the same name mapped to that login. When you
restore this database, you may find that the user Alfons now maps
to the login Sture - or no login at all.

There is a stored procedure sp_change_users_login which takes of this
for SQL logins. I seem to recall that SP2 has something for Windows
logins as well, but I don't recall it right now.

If you believe that the synchronization function in Navision should
handle this remapping, I suspect that you may be a better off opening
a support case with the vendor.

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Relevant Pages

  • Re: System Administrator Implied Permissions
    ... > sa login, it assigns it the System Administrator fixed ... > Now, given this, why does SQL Server ... in each database is always a member of the public and db_owner roles. ... Other sysadmin role members have the exact same ...
    (microsoft.public.sqlserver.security)
  • Re: cannot login to the db after...
    ... Jasper Smith (SQL Server MVP) ... I have created a new database, "db_1", using the "sa" ... I then created a new login, "sqluser1" and gave ...
    (microsoft.public.sqlserver.security)
  • Re: Cant view merge agent properties (trying again)
    ... In the List of Actions for the Snapshot Agent History I see this repeated: ... every single database listed. ... So, just now, I went to computername\Administrator Login ID (because it's ... On the computer running SQL Server, ...
    (microsoft.public.sqlserver.replication)
  • Reading data from Navision to SQL Server: Date issues
    ... SQL Server 2000 database via ODBC. ... This notation is the one required by C/ODBC Navision driver. ...
    (comp.databases.ms-sqlserver)
  • Re: Unexpected Login Screen When Accessing SQL Data Via .NET Intra
    ... >> data and the SQL Server is on the same server. ... >> My problem is that one user has started getting a login popup ... >> right in via Windows Authentication like everyone else and like they ...
    (microsoft.public.sqlserver.connect)