0

Window account is getting the below error when trying to connect into the SQL instance via management studio 2014.

The SELECT permission was denied on the object 'configurations', database 'mssqlsystemresource', schema 'sys' (Error 229)

The SQL instance is 2014, the user doesn't get the error if I make the login sysadmin.

  1. The login has connect permission to the instance and even if i give it dbowner to the master database it cannot connect.
  2. Default database is master also.
  3. It is not an orphaned user
  4. I have tried the GRANT VIEW DEFINITION - no luck
  5. I have checked and the login doesnt have any DENY permissions

When i go into the properties of the login > Securables > effective tab > it gives the error:

The SELECT permission was denied on the object 'fn_my_permissions', database 'mssqlsystemresource', schema 'sys'.

So that is obviously related also

Any ideas?

Improve this question
6
  • Check the PUBLIC role permissions. Make sure there is SELECT permissions granted to this role.
    – samosql
    Jun 21, 2018 at 13:35
  • Yep, has all the necessary permissions there :(
    – Daniel Nash
    Jun 21, 2018 at 14:19
  • There are numerous reasons for this error but most common is when db_denydatawriter and db_denydatareader is assigned to login. I know you mentioned that login has no deny permissions. Remember DENY permission overrides any access permission.
    – samosql
    Jun 21, 2018 at 14:39
  • Hi Samosql, there is no deny permissions anywhere and it select on the public role. I run execute as user = 'windows user' -- Set this to the user name you wish to check select * from fn_my_permissions(null, 'DATABASE') -- Leave these arguments, don't change to MyDatabaseName order by subentity_name, permission_name revert & it throws the error Msg 229, Level 14, State 5, Line 2 The SELECT permission was denied on the object 'fn_my_permissions', database 'mssqlsystemresource', schema 'sys'.
    – Daniel Nash
    Jul 9, 2018 at 10:39
  • check out this similar post: dba.stackexchange.com/questions/123119/… It is very similar to what you are experiencing and might help you. You should look into granting select to the object/view mentioned in the mssqlsystemresource db.
    – samosql
    Jul 9, 2018 at 19:42

1 Answer 1

Reset to default
1

The answer was in fact the logins being in a AD group account also which I didn't see that they were members of and someone had selected all permissions on the master database so obviously selecting denydbreader/denydbwriter.

My fault for missing this group out from my checks!

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.