June 5, 2019 at 5:57 pm
Receiving this error when users are trying to log in through SSMS and can repeat it by using SETUSER and attempting query.
Msg 229, Level 14, State 5, Line 2
The SELECT permission was denied on the object 'databases', database 'mssqlsystemresource', schema 'sys'.
I had new VM's created to replace some older ones for several machines and used sp_help_revlogin to move over the logins. I did this on 5 machines of which all but one works. I have tried to set permissions on mssqlsystemresource with no luck and moved the ldf and mdf files from a working machine with no luck. I have read many articles saying that public permissions were removed, but I know that was not done and besides I can't seem to apply any permissions to that database. Anyone else every seen this or have a solution?
June 5, 2019 at 7:11 pm
June 5, 2019 at 8:54 pm
The logins are on five machines and are all AD accounts. My dev team is all under one AD account and can access 4 out of the 5 machines. I don't think the problem is with any of the user databases, we get the error when they try to connect and also if I use SETUSER and just try to read something like select * from sys.databases. If the accounts are elevated to sysadmin, they work of course.
June 6, 2019 at 12:03 am
The permissions would be on the catalog views - at least when you select from sys.databases. Did you check the permissions on the view? Are there other errors in the error logs - maybe the instance is wonky? Is the user mapped to the public role - you can check the user and their roles with something like:
Use Master;
EXECUTE AS LOGIN = 'LoginYouAreUsingToTestThis';
SELECT SUSER_SNAME(sid), name, type
FROM sys.user_token;
REVERT;
Sue
June 6, 2019 at 2:47 pm
I get the exact same error.
June 6, 2019 at 4:15 pm
You got the error by checking the SQL Server error log and when checking the permissions for sys.databases?
Then trash of the VM and start over.
Sue
June 6, 2019 at 5:21 pm
As a thought... I wonder if MAYBE the user is a member of a group that has deny permissions on a system object but you also granted the permissions explicitly.
What happens if you run:
USE [Master];
EXECUTE AS LOGIN = 'LoginYouAreUsingToTestThis';
SELECT SUSER_SNAME(); --Just to verify that the impersonation worked
SELECT * FROM fn_my_permissions('sys.user_token','object');
SELECT * FROM fn_my_permissions('sys.databases','object');
REVERT;
I am expecting that they have no permissions on either of those tables (possibly on the whole of the sys schema). Even if you grant them explicit permissions on the object, if they are part of a role that has deny permissions, the DENY will take precedence over the GRANT.
Another thing to check would be the public role on master. Did somebody go through and deny SELECT on an object (such as sys.databases)?
If public permissions are good, I would look at the roles the logins logins are members of. If they are a member of anything other than public, maybe try removing them from those to see if it helps. Could be somebody was trying to be helpful and created a role on the server to deny selecting from sys schema objects and unintentionally broke things.
My final thought on this is are you certain they have permissions on the original system you cloned things from? If you still have access to that system, might not hurt to test that out. I know I've had users tell me that they lost permissions to things when we do some transition work and when I look at the original system, they never had permissions; they just worked with others in their department who had permissions and thought they should have permissions too.
If you saved your scripts for the user creation and permission setup, maybe look through those for anything that would do either:
DENY SELECT ON [sys]
or
DENY SELECT ON sys
it might give you an idea as to why they cannot pull data from those objects.
All else fails, nuke it and try setting up things again as Sue_H suggested.
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.
June 6, 2019 at 5:33 pm
It could be a deny or what happens more often is the permissions were removed from public. Or guest disabled - which would cause a whole bunch of other issues which would likely be in the error log. The errors are for the catalog views which by default public has select permissions. You don't grant permissions on anything in the resource database directly.
But if you get the error when a sysadmin tries to view the permissions on sys.databases and when looking at the SQL Server error log then something isn't right. Or it was just an answer thrown out there. Kind of hard to help out if that's the case.
Sue
June 20, 2019 at 4:36 am
A user does not require explicit SELECT permissions on the table to view data through a VIEW, as long as the user has SELECT permissions on the VIEW.
Granting the user SELECT permissions on the VIEW will stop this error message appearing.
In a situation of granting permissions to a specific object ( as opposed to the wider db_datareader role), I prefer to create a ROLE and then assign the ROLE to the user permission set.
SQL Database Recovery Expert 🙂
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply