When you're a partner .. you deal with databases of your customers, right? At least - you should ;-). Usually, there is "some" security set up at the customers site. At least let's hope so ;-).
It might happen that you want to copy a customer's database to your site, just to test, debug, do extra development, test why that MRP won't run, do nasty things, .. you know .. do partner stuff.
What many of us do, is: take a backup of that database (or company) and restore it locally in the office. Problem is: there is security set up in that database, so there is no way to get in, even after a restore, we get this:
In NAV2009, we had the chance (at least in the native restore) to reset the security immediately after the restore, but in NAV2013, we first have to be able to log into the database in RTC to be able to set up security. So there's no obvious "bridge" anymore that we can take.. .
How do we force ourselves into that DB?
Your first guess would be .. thinking of NAV2013 and all the new great features in it …
Powershell!
That would be a safe bet, wouldn't it? In NAV2013, you have all these great new Cmdlets at your disposal …
And as you can see .. you can set up security. My guess would be: let's add myself into the system.
So, first, I look up my sid in a normal command shell (not powershell) with
wmic useraccount get name,sid
I copy the Sid I want, go back to Powershell, and do this:
New-NAVServerUser TestFBKRestore -sid MyCopiedSid
The result is somewhat disappointing .. but understandable:
You obviously need access to be able to add users.. .
What about SQL Server?
We all know the trick in the old days to reset security straight on SQL Server - deleting the content of certain tables... Well .. may be it's also possible in NAV2013.
I think it's safe to assume that the person who is trying to restore that database, is sysadmin on the SQL Server where it's restoring the database. Well, run this script on your new database:
delete from [dbo].[User]
delete from [dbo].[Access Control]
delete from [dbo].[User Property]
And you'll see you'll be able to get back in :-). In fact .. It's even better to clear a few more tables, just for deleting redundant data
delete from [dbo].[Page Data Personalization]
delete from [dbo].[User Default Style Sheet]
delete from [dbo].[User Metadata]
delete from [dbo].[User Personalization]
Hope it's useful for you.
And of course .. If you have comments or a better way .. I definitely want to know! Leave a comment :-).
Amazingly, more than 3 years after this post was published, then it's still in the top 5 most popular post the last 30 days.
The most popular blog post on DUG the last 30 days! Very good for an almost 3 years old post. :)
Thanks, Jason :-)
For feedback reasons; your SQL script worked a treat. Thanks.
Try to delete the content, restart the service .. and afterwards add the user in NAV.
I am getting the same above error when I use username Authentication.I assigned super role to the username,but still iam getting the error.
That's Always handy ;-). It's not 100%, but it takes away at least 80% of the work.
What is your personal blog? do you have a link?
Also, please note this is my old blog. My new one is here: www.waldo.be.
and this very post is also on that one as well:
www.waldo.be/.../nav-2013-how-to-log-in-any-database
No - did not do a restart. Thanks, I will try that next time first.
I also need to switch my personal blog to a site that has spam control like yours.
Hey Dave,
did you try to restart your service tier after you deleted the entries?
I had to set up a second service for a ISV supplied database. After running your deletes, I received the cannot execute application codeunit 1 message, so I added my user info (from my other database) into your top 3 tables and that allowed me to login.
Next time I will try Peter's tip instead of hand entering.
I had the same issue,
I was trying to connect to my local NAV service, which wasn't installed on my pc.
Got the error everytime I tried to start my RTC 2013 Client.
The reason why I got the error was the our System Administrator installed the 2013 client using our network NAV Service.
So after having installed a local NAV service and dBcomponents I couldn't connect due to the fact that my local client was preinstalled with settings for our Server.
So I deleted the "ClientUserSettings.config" and bingo, problem solved.
C:\Users\<username>\AppData\Roaming\Microsoft\Microsoft Dynamics NAV\70
Conclusion there to many config files for NAV.....lol
Thanks, Severin. You're probably right that I forgot to mention this quite important thing :-)
Just to be thorough, I just had the issue that I've locked myself out of a database and deleteted the records described here - afterwards had to do a NST Service Restart.
You're very right.. . And makes sense!
thanks for sharing! :-)
I needed this today. I was working on the TSQL way same as Peter, I was just missing the Access Control table.
So... Already Thanks!
BTW... My scenario is moving the the SQL backup from our server to the client server, someday I may need it back again, so if I leave my existing logins intact, it would save a couple of minutes.
Thanks again!