Know about SQL Server Error 916 and How to Fix It!

来源:转载

SQL Server

SQL Server is a relational database management system developed by Microsoft while focusing on large enterprise environment work over a network. The First version of SQL Server is 1.0 and released in the year 1989, the latest version is MS SQL Server 2016 Community Technology Preview 2.4 (CTP 2.1) released on September 30, 2015.

Problem

Accessing the SQL server database by using SQL Server Management Studio having a limited number of permissions (database read or write) may produce the SQL Server Error 916 and the message displayed on the screen is – “The server principal usernameis unable to access the database databasenameunder the current security context.”

Causes of SQL Server Error 916

The main problem is due to bugs present in SQL Server Management Studio that prevents the user to connect to the database and refuses to display the database list. There may be several other reasons behind the specific error code 916.

The user is not permitted to view the data of selected column within the database. The database is currently not available. There are multiple columns such as Size, Space available, Data Space Used, Default file group, Index Space Used, Mail host, Primary file path and user has added at least one of them to the list of Object Explorer Details. If the Auto Closeoption for the database is enabled, then Collationcolumn cannot be retrieved by SQL Server Management Studio (SSMS). For a database, Collation column contains NULL.

Due to the above causes, the error message will be displayed to the user.

Symptoms

The symptoms of the SQL Server error 916 may vary depending upon the different scenarios explained further:

Symptom 1:

In SQL Server 2008, if a user tries to expand the database folders under a node even if he is not a member of a Sysadmin fixed server role or does not have the authority to access the database. If the guest user wants to expand the database nodes, connection permission is required otherwise; the following error message will be displayed:

Failed to retrieve data for this request (Microsoft.SqlServer.Manager.Sdk.Sfc)

Additional information:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Symptom 2:

In SQL Server 2005, if a guest user who is neither a member of a Sysadmin fixed server role, nor having the permissions is able to expand the database node but cannot view the database properties. In this case, the user will receive the following error message:

Cannot show requested dialog.(SqlMgmt)

Additional Information:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Note:The error message is displayed even though the user is a database owner of that database.

Symptom 3:

A product of the Microsoft family, Microsoft Office Communications Server 2007 (OCS 2007) is affected by this issue and the services will not start even after the server is started. The following error message will be invisible in the Event Viewer:

Event Type: Error Event Source: OCS User Services Event Category: (1006) Event ID: 30962 Date: Time: User: N/A Computer: ‘computer name’

Description: Connection to back-end database succeeded, but failed to execute registration-stored procedure on back-end. This error should not occur under normal operating conditions. Contact product support.

Back-end Server: ‘server name’ Database: ‘database name’ Sql Native error: 916 Connection string of: driver={SQL Native Client}; Trusted Connection=yes; Auto Translate=No; Server=’server name’; Database=’database name’

Cause: Possible issues with back-end database.

Resolution: Ensure the back-end is functioning correctly.

The above-mentioned symptoms are most common and lead to the Error 916 for SQL Server Management Studio. If the user found any of them, he may try the best possible solutions described below in order to remove the error.

Resolving the Symptoms

In order to view the granted permissions for the guest-user, the following command is helpful if run by a member of a Sysadmin fixed server. The query is as follows –

USE msdb;

SELECT prins.name AS grantee_name, perms.*

FROM sys.database_permissions AS perms

JOIN sys.database_principals AS prins

ON perms.grantee_principal_id = prins.principal_id

WHERE prins.name = ‘guest’ AND perms.permission_name = ‘CONNECT’;

GO

On running the above command, a table is presented to the user containing all the attributes of guest user.

However, an empty result shows that guest user is disabled in the database and again the SQL Server error 916 will be displayed. To overcome the error you may perform the following available solutions.

How to Remove the SQL Server Error 916:

To remove the error for accessing the database, the user can perform the following solutions. Any one of them will help you.

Solution No. 1 Select the Object Explorer Detailsunder Viewsection within the SSMS. Right click on Column Headerand deselect Collation. Refresh the server and operate a database. Solution No. 2 Click on View > Object Explorer detailsfrom the menu option of SSMS. Within Object Explorerwindow, click on Databasefolder. Right – click on the column header and select Reset View. In order to perform the final step, refresh the Database folder. Solution No. 3 Check the Auto Close setting of the database, set it to False and proceed further.

The above-described solutions may resolve the problem and the user can proceed the workflow.

Conclusion

With the help of this page, an individual can be aware about the SQL Server error 916, the problems occur due to it, what are the causes. Thus, a user can get the detailed knowledge about the SQL server and the possible error message displayed if the guest user wants to expand the database node in order to view its data.



分享给朋友:
您可能感兴趣的文章:
随机阅读: