May 12, 2020 at 2:08 pm
HI,
I have restored database from SQL 2016 to SQl 2017, All permission and logins were copied and applied as per the standard process.(fixed the orphan user)
There are some schema and respective database roles available in database.
For ex. A user can access only A schema same has applied however A use accessing B Schema even after restricted permission
Post restoration user are seeing multiple schema in SQL 2017 but in case of 2016 respective schema's visible from application end.
Please help .
May 12, 2020 at 4:15 pm
To me it sounds like permissions were set wrong likely when fixing the orphan users OR when copying the logins and roles over.
If you still have the 2016 instance around, my first step would be to compare logins between the 2 systems. For example, if an AD group exists on the new system that doesn't exist on the old, that could be doing something with permissions.
If the logins match between the 2 systems, then my next step would be to look at the logins and the permissions granted on 2016 vs 2017. Once you are sure the logins are the same, I would be looking at the instance and database roles. Make sure those are set the same between the two systems. Lastly, I'd be checking the users to make sure things are the same. That last step likely isn't needed as you just restored the database and fixed the orphan users (ALTER USER <username> WITH LOGIN=<loginname>) so you shouldn't have been adding any additional permissions while fixing the orphaned users.
Without physical access to your system, all I can do is guesswork and tell you my process for this. I would find one user having the problem and compare between the two systems.
Now if the 2016 instance is gone, then things are a bit more tricky to figure out, but it works out to be a similar process. Pick one user who has access to things they shouldn't, impersonate them and verify that they indeed DO have permissions where they shouldn't. I say to do this just to make sure it isn't something weird at the application side as that could be connecting to SQL with a hard-coded account or be using impersonation on its side. Once you confirm the user can indeed access things they shouldn't be able to, look at what roles they are in and figure out what role is giving them access. If after investigating it appears they shouldn't have access but still do, start looking into AD groups that have permissions.
To do this easier, you can script it. Impersonate the user and then look at some functions and/or views. For example, you can have a look at the view [sys].[User_Token] after impersonating the user to see what roles they are in on the system (including nested roles) which may help to figure out the permission chain. Or have a look at the function [sys].[fn_my_permissions] after impersonating the user. That should help you narrow things down.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply