Synchronize Security Error [sp_revokedbaccess]

klavin

Hey everyone, I've been a patron of your knowledge on the forums for a while, but finally I have fallen into a problem on the SQL side that stopped me in my tracks. Looks like I have no excuse and its time to join the ranks of the posters here...I'm the in-house programmer on the customer side, running version 3.7B (on 4.0 SP 3 executable) with SQL Server 2005.

During adding a user yesterday, I was given the following error; which I can get to repeat unlimited number of times by trying to Sychronize security...
Tools > Security > Synchronize All

Or by trying to modify the "Name" in the Database Logins.

The following SQL Server error(s) occurred:

15138,"42000",[Microsoft][ODBC SQL Server Driver][SQL Server]The database principal owns a schema in the database, and cannot be dropped.

SQL:
{CALL [sp_revokedbaccess](?)}

I searched the forum for "sp_revokedbaccess" figuring that would be the closest to the problem I have, but only came up with one result on adding users from within Navision.

My knowledge of SQL 2005 is lacking - but I am trying to learn more. Any assistance you can give me would be greatly appreciated in setting me to the right direction.

We have always done the same process for adding the users, I know it has something obviously to do with the schemas, but it was a management consulting firm that set up the SQL install.

Again, thanks to everyone for the help on here many of times over, and for any information you can give me.

--Kevin

kine

It seems that some user for which the access is dropped (the user was deleted or something like that) is owner of some schema on the DB. Check the schemas through Management studio and try to change or remove the owner of the schema.

And do not forgot, that it is better to use SP3 and switch the Security model to Standard. You do not need to sync the security...

nunomaia

When you try to delete a database user and an error in synchronization happens, Navision can have try to delete user without deleting user in scheme.
Remove user from schema. Synchronize users again.

I would also recommend using Standard Security model to avoid such errors.

klavin

True... I am actually on the Standard security model... Let me elaborate a little.

As I mentioned thats where I can get the error also, I originally got the error on creating a new user:
I inserted the record, and accidently tabbed one to many times and it went off the record. I went back and filled in the "Name" Field. Thats where it was bombing.

I will start looking at the schema and try to figure that side of it out. Thanks for your help - i'll post my findings and [Solved] it.

JanM

We got the same error, also after deleting the schemas and the users from the db. The existing logins are assigned to other nav dbs on the same instance, so these users holds the schema with the same name which we deleted on the other db, where should be synchronized. Must the logins be droped? I hope for another solution...

JanM

Found the solution: NAV checks all user in the sql server 2005 db while synchronizing, also if they are not defined in NAV. We added a user for other reasons like datatransfer or reporting with a schema binding. So finaly we could only change the schema owner to have a successfull synchronization. This looks like a limitation, you will often add users with appropriate schemas, may done by an application. The NAV db is no more native, it is open for other processes and applications.

ara3n

Ran into this problem today. The server db_reader role was assign to some other use. I changed it to db_reader and synchronization worked.

klavin

It has been a long time since this post was around, sorry for bringing it back up. Aaron, I found the problem thanks to your help.

Somepoint the company merger the new SQL server was setup by a management consulting group that changed the owners of:

db_datareader
db_datawriter

Changing them back to respective owners resolved the problem. I wanted to come back and close this one as solved just haven't got around to doing it.

ara3n

That's great.