Cannot connect . . . Error 229 Select permissions denied . . .

[Sheldon]

Hello -

Someone is receiving the following error when trying to connect to Sql Server:

Cannot connect to [serverName]
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum) An
exception occurred while executing a Transact-SQL statement or batch
(Microsoft SqlServer ConnectionInfo) The SELECT permission was denied on the
object 'configurations', database 'mssqlsystemresource', schema 'sys'.
(Microsoft SQL Server, Error: 229)

This is our production server. I am reluctant to mess around with Select
permissions at all. I always create stored procedures and give execute
permissions.

This person had been able to connect previously. Any idea what may be
causing this problem?

Sheldon

[Bob Simms]

Post by Sheldon
Hello -
Cannot connect to [serverName]
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
An
exception occurred while executing a Transact-SQL statement or batch
(Microsoft SqlServer ConnectionInfo) The SELECT permission was denied on the
object 'configurations', database 'mssqlsystemresource', schema 'sys'.
(Microsoft SQL Server, Error: 229)
This is our production server. I am reluctant to mess around with Select
permissions at all. I always create stored procedures and give execute
permissions.
This person had been able to connect previously. Any idea what may be
causing this problem?

The sys schema is in effect an alias to the Resource database. This is
where all the definitions for the metadata are held. It sounds as though
he's trying to do something like list available databases or some other such
metadata. I don't suppose he's a member of denydatareader, is he?

[Erland Sommarskog]

Post by Sheldon
Cannot connect to [serverName]
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
An exception occurred while executing a Transact-SQL statement or batch
(Microsoft SqlServer ConnectionInfo) The SELECT permission was denied
on the object 'configurations', database 'mssqlsystemresource', schema
'sys'. (Microsoft SQL Server, Error: 229)
This is our production server. I am reluctant to mess around with Select
permissions at all. I always create stored procedures and give execute
permissions.
This person had been able to connect previously. Any idea what may be
causing this problem?

Sounds like someone would have revoked the permissions on sys.configurations
for the public role. Or denied access to this view to this particular user.

I played with this, and indeed this is possible to do. But to deny a
login access to sys.configurations, I had to first make him a user in
the master database! And when I dropped him from master, he again login.

So examine whether this user appears in master.sys.database_principals,
and whether other users do. Normally, logins should not be users in
master.