asked
Select permission denied on object 'configuration', database 'mssqlsystemresource'
Hi all, I´m investigating a strange behaviour on a SQL Server 2008 SP2 installation. One of my colleagues just wants to access the instance with SSMS and gets the error: The SELECT permission was denied on the object 'configurations', database 'mssqlsystemresource', schema 'sys'. (Microsoft SQL Server, Error: 229) The account trying to access is not in the sysadmin group and also has no mapping to the master DB (e.g. no denydatareader on sys.configurations). I just granted the VIEW Server State permission, but the error still exists. If we now just open a query window and connect to that instance, then there´s no error. When we try to query sys.databases or sys.configurations the SELECT permission denied error comes up again. The public role also has Select right to sys.configurations (checked with sp_helprotect) Any ideas where to look at to solve this issue? Regards Dirk
I would double check the user and the public role and make sure that you haven't accidentally denied them access. This is definitely a permissions issue.
I got it!!!! The account was member of a groupand this group was also member of a 2nd group. And guess what? There´s a deny datareader for that group inside master. Now I have to ask my other colleagues who set the deny permission....
It can be a pain in the a**. But I´m still a little bit confused why xp_logininfo 'mydomain\user','all' did not show all groups. I´ll investigate it tomorrow. Thx again for the advice
