Mssql error 916

Wondering how to fix SQL Server Error 916. We can help you.

Often while connecting to SQL Server database using SQL Server Management Studio with limited permissions, we may face Microsoft SQL Server error 916.

Here at Bobcares, we often get requests from our customers to fix similar Microsoft SQL server issues as a part of our Server Management Services.

Today, let’s see how our Support Engineers fix this error for our customers.

What causes SQL Server Error 916

A typical error message is given below:

Following are some of the causes for this error:

1. When a user with limited permissions tries to view the column data in a selected header.

2. At times, the database may be in offline mode.

3. Several databases with different collations are on the same instance, that is why SQL Server Management Studio is unable to retrieve the collation.

4. The database is configured to an auto-closed state.

5. Sometimes the database may get corrupt due to some malware, improper shutdown, or when the database goes in suspect mode.

How to fix SQL Server Error 916

Following are the two methods to fix this error.

1. Via SQL Server Management Studio (SSMS)
2. With the help of T-SQL

Steps for Fixing the error 916 via SSMS

1.  Firstly, connect the SQL Server instance having valid credentials using SSMS.

2. And go to the Object Explorer Details followed by the View Menu or press F7 for the same.

3. After that, open the Databases folder.

4. Then go to the object explorer and right-click to open the column header.

5. After that, right-click on the column header and uncheck all of the optional columns.

6. Finally, click the refresh button.

Now we will be able to see the database.

Often unchecking the COLLATION option automatically fixes the issue.

Steps for Fixing error code 916 via TSQL Query

We can connect to the database via Transact-SQL. Grant the specific login access to the named database TEST_DB, for instance.

We can use the following syntax:

USE msdb;
GO
GRANT CONNECT TO [TEST_DB/BOB];

Now we can try either of the following to fix this error.

1. Disabling Collation

1. First, we need to select the Object Explorer Details under the View section within the SSMS.

2. Then right-click on Column Header and deselect Collation.

3. Finally, refresh the server and operate a database.

2. Disabling the Auto Close setting of the database

Here we need to check the Auto Close setting of the database, set it to False, and proceed further.

[Need assistance? We can help you]

Conclusion

In short, we saw how our Support Engineers fix SQL Server Error 916 for our customers.

var google_conversion_label = «owonCMyG5nEQ0aD71QM»;

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

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 username is unable to access the database databasename under the current security context.”

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 getting SQL Server Error 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 Close option for the database is enabled, then Collation column 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.

SQL Error 916 – 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: SQL Server error 916 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 SQL Server 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

1. Select the Object Explorer Details under View section within the SSMS.
2. Right click on Column Header and deselect Collation.
3. Refresh the server and operate a database.

Solution No. 2

1. Click on View > Object Explorer details from the menu option of SSMS.
2. Within Object Explorer window, click on Database folder.
3. Right – click on the column header and select Reset View.
4. In order to perform the final step, refresh the Database folder.

Solution No. 3

1. 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.

Fix Microsoft SQL Server Management Studio Error 916

Introduction to SQL Server Error 916

While connecting to SQL Server database using SQL Server Management Studio with limited permissions you may face MS SQL Server error 916. In this article we will discuss how to fix the error 916 in SQL Server.

Error message

The server principal «Login Name» is not able to access the database «database name» under the current security context. (Microsoft SQL Server, Error: 916).

Reasons of MS SQL Server Error 916

In this section I have discussed the possible reasons responsible for the error no 916 in SQL Server.

1. User with limited permissions trying to view the column data in a selected header.
2. It is possible that the database is in offline mode or it may also possible that the collation is NULL.
3. Several databases with different collations are on the same instance, that’s why SQL Server Management Studio is unable to retrieve the collation.
4. The database is configured to auto-closed state.
5. It’s also possible that the database gets corrupted due some malware, improper shutdown or the database has gone in suspect mode.

Fix Error Number 916 in Microsoft SQL Server

There are two methods to fix the error. The first method is via SQL Server Management Studio and the next one with the help of T-SQL. Let’s take a tour at step by step solution of this error.

Fix error 916 via SSMS

1. Firstly, connect the SQL Server instance having valid credential using SSMS.
2. Now, go to the Object Explorer Details followed by the View Menu or you can press F7 for the same.
3. After this, open the Databases folder.
4. Now you can see the properties of the database like Database Name, Collation, Data Created, Last Backup Created Date, Size in (MB), data space in (KB)etc. but in the column header, you will see there are no user defined databases.
5. Now, go to the object explorer > right click on them to open the column header > right click on the column header and un-check all of the optional columns.
6. After doing this, now click the refresh button and you will be able to see your database.

Note: In most of the cases the un-checking of the COLLATION option automatically fix the issue.

Fix error code 916 via TSQL Query

You can connect to the database via Transact-SQL. Grant the specific login access to the named database.

Syntax:

USE msdb ;
    GO
    GRANT CONNECT TO [TEST_DB/CLARK] ;

Conclusion

In the present article we have discussed about the Microsoft SQL Server error 916. This article describes why the error occurs and how can we fix this issue via SQL Server Management Studio and Transact-SQL.

MSSQLSERVER_916

Применимо к: SQL Server (все поддерживаемые версии)

Сведения

attribute	Значение
Название продукта	SQL Server
Идентификатор события	916
Источник события	MSSQLSERVER
Компонент	SQLEngine
Символическое имя	NOTUSER
Текст сообщения	Серверу-участнику «%.*ls» не удалось обратиться к базе данных «%.*ls» в текущем контексте безопасности.

Объяснение

У имени входа отсутствуют необходимые разрешения для соединения с именованной базой данных. Имена входа, которые могут использоваться для подключения к этому экземпляру SQL Server, но не имеют специальных разрешений в базе данных, получают разрешения пользователя guest. Это одна из мер безопасности, которая не позволяет пользователям одной базы данных подключаться к другим базам данных, в которых они не имеют прав доступа. Это сообщение об ошибке отображается, если у пользователя guest отсутствует разрешение CONNECT для подключения к именованной базе данных и свойство доверительных отношений не включено. Это сообщение об ошибке отображается, если у пользователя guest отсутствует разрешение CONNECT для соединения с именованной базой данных.

Если разрешение CONNECT для подключения к базе данных msdb не предоставлено или отозвано, такая ошибка может произойти в среде SQL Server Management Studio при попытке обозревателя объектов отобразить состояние управления на основе политик для всех баз данных. Обозреватель объектов использует разрешения текущего имени для входа, чтобы получить эту информацию из базы данных msdb, что и вызывает указанную ошибку. Также отображается следующее сообщение об ошибке:

Ошибка при получении данных по этому запросу. (Microsoft.SqlServer.Management.Sdk.Sfc)

Действие пользователя

Прежде чем обойти эту меру безопасности, необходимо достичь четкого понимания того, какие пользователи проходят проверку в различных базах данных. Следующие методы могут позволить пользователям, имеющим разрешения в одной базе данных, подключаться к другим базам данных, в результате чего доступ к данным может получить злонамеренный пользователь. Если автономные базы данных включены, владельцы этих баз данных могут выполнить следующие шаги в одной базе данных, чтобы предоставить доступ к другой базе данных на экземпляре SQL Server.

Можно подключиться к базе данных одним из следующих способов.

Предоставить имени входа специальный доступ к указанной базе данных. В следующем примере предоставляется разрешение для имени входа для доступа Adventure-WorksLarry к базе данных msdb .

Предоставить для пользователя guest разрешение CONNECT для базы данных, указанной в сообщении об ошибке. В следующем примере пользователю CONNECT предоставляется разрешение msdb для базы данных guest .

Включите свойство TRUSTWORTHY в базе данных, применительно к которой прошел проверку пользователь.

Источник

MSSQLSERVER_916

Applies to: SQL Server (all supported versions)

Details

Attribute	Value
Product Name	SQL Server
Event ID	916
Event Source	MSSQLSERVER
Component	SQLEngine
Symbolic Name	NOTUSER
Message Text	The server principal «%.*ls» is not able to access the database «%.*ls» under the current security context.

Explanation

The login does not have sufficient permissions to connect to the named database. Logins that can connect to this instance of SQL Server but that do not have specific permissions in a database receive the permissions of the guest user. This is a security measure to prevent users in one database from connecting to other databases where they do not have privileges. This error message can occur when the guest user does not have CONNECT permission to the named database and the trustworthy property is not set. This error message can occur when the guest user does not have CONNECT permission to the named database.

When CONNECT permission to the msdb database is denied or revoked, SQL Server Management Studio can receive this error when Object Explorer tries to show the Policy Based Management status of each database. Object Explorer uses the permissions of the current login to query the msdb database for this information, which causes the error. The following error message also occurs:

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

User Action

Before circumventing this security measure be sure to have a clear understanding of users are authenticated in various databases. The following methods may allow users that have permissions in one database to connect to other databases which could expose data to a malicious user. When contained databases are enabled, the following steps can allow database owners in one database to grant access to other database on the instance of SQL Server.

You can connect to the database in one of the following ways:

Grant the specific login access to the named database. The following example grants the login Adventure-WorksLarry access to the msdb database.

Grant the CONNECT permission to the database named in the error message for the guest user. The following example grants the CONNECT permission to the msdb database for the user guest .

Enable the TRUSTWORTHY property on the database that has authenticated the user.

Источник

Microsoft sql server error 916

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Asked by:

Question

I migrated an SQL Server 2008 R2 to a new domain with a new ip address. I used this DB to connect to a Mcafee EPO.

After the migration, I can´t connect to the database. I receive the menssage : The server principal «NEWDOMAINnewadministrator» is not able to access the database «DATABASENAME» udner the current security context. (Microsoft SQL Server, Error: 916)

How can I configure my new administrator with the new domain to this database?

Refer following link

Please Mark it as Answered if it answered your question OR mark it as Helpful if it help you to solve your problem.

I added the new user of the new domain to Logins file NEWDOMAINnewuser. I chose Porperties and in Default database I chose the database OLDSERVER. When I chose the option user Mapping to do it db_owner I receive a message One or more database are inaccessible and will not be dispalyed in list, I do Ok but my database doesnt appear, just I have master, msdb and tempdb in database (not my OLDSERVER database).

Im using Microsoft Sql management studio to do this step

Tibor Karaszi, SQL Server MVP (Web Blog)

After I created the new user with the new domain I logged in the microsoft management console with windows authentication. I did click in the DB and I receive the message: The database ePO_OLDSERVER is not accessible (ObjectExplorer).

— NEWNAMESERVEREPOSERVER(SQL server 10.50.2500 — NEWDOMAINnewuser

Receive the message: the database. is not accesible

— NEWNAMESERVEREPOSERVER(SQL server 10.50.2500 — NEWDOMAINnewuser

So you have SQL 2008 R2 SP1. You should install SP3 as soon as possible. It will not resolve your issue, but you should not be an outdated version.

When you say that you created a new user, was that on server level or user level?

I did click in Security, Logins (right click), New Login, Windows Authentication I did Search in Login Name, I chose Location my New active directory (choosing my new domain), Entering my new administrator user.

When I created this user in Logins of the MSQLMC I chose in default database ePO_OLDSERVER.

If I chose Use Mapping and chose master database (because epo_OLDSERVER doesnt appear) I tried to put db_owner and I receive the message error: Microsoft SQL error, 15247

Created failed for User ‘Newdomainnewuser’ (microsoft.sqlserver.Smo)

User does not have permission to perform this action Microsoft SQL error, 15247

So, I don’t know how to configure the permission to my new user of the new domain to my database.

Источник

Microsoft sql server error 916

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Asked by:

Question

I migrated an SQL Server 2008 R2 to a new domain with a new ip address. I used this DB to connect to a Mcafee EPO.

After the migration, I can´t connect to the database. I receive the menssage : The server principal «NEWDOMAINnewadministrator» is not able to access the database «DATABASENAME» udner the current security context. (Microsoft SQL Server, Error: 916)

How can I configure my new administrator with the new domain to this database?

Refer following link

Please Mark it as Answered if it answered your question OR mark it as Helpful if it help you to solve your problem.

I added the new user of the new domain to Logins file NEWDOMAINnewuser. I chose Porperties and in Default database I chose the database OLDSERVER. When I chose the option user Mapping to do it db_owner I receive a message One or more database are inaccessible and will not be dispalyed in list, I do Ok but my database doesnt appear, just I have master, msdb and tempdb in database (not my OLDSERVER database).

Im using Microsoft Sql management studio to do this step

Tibor Karaszi, SQL Server MVP (Web Blog)

After I created the new user with the new domain I logged in the microsoft management console with windows authentication. I did click in the DB and I receive the message: The database ePO_OLDSERVER is not accessible (ObjectExplorer).

— NEWNAMESERVEREPOSERVER(SQL server 10.50.2500 — NEWDOMAINnewuser

Receive the message: the database. is not accesible

— NEWNAMESERVEREPOSERVER(SQL server 10.50.2500 — NEWDOMAINnewuser

So you have SQL 2008 R2 SP1. You should install SP3 as soon as possible. It will not resolve your issue, but you should not be an outdated version.

When you say that you created a new user, was that on server level or user level?

I did click in Security, Logins (right click), New Login, Windows Authentication I did Search in Login Name, I chose Location my New active directory (choosing my new domain), Entering my new administrator user.

When I created this user in Logins of the MSQLMC I chose in default database ePO_OLDSERVER.

If I chose Use Mapping and chose master database (because epo_OLDSERVER doesnt appear) I tried to put db_owner and I receive the message error: Microsoft SQL error, 15247

Created failed for User ‘Newdomainnewuser’ (microsoft.sqlserver.Smo)

User does not have permission to perform this action Microsoft SQL error, 15247

So, I don’t know how to configure the permission to my new user of the new domain to my database.

Источник

Introduction

This article explains step-by-step process on how to fix the error 916 in SQL Server.

The error message prompts as follows when users face the error in MS SQL Server Management Studio:

Msg 916, Level 14, State 1, Line 1

The server principal «login_name» is not able to access the database «database_name» under the current security context.

You can also check the complete documentation for the error message here: MSSQLSERVER_916

The level 14 means that it is a security error and it is related to a grant permission

System Set-up Requirements

The following is a list of prerequisites

Any SQL Server version installed.

• The SQL Server Management Studio (SSMS) installed on the machine.

Getting started

This error message means that the logged in user does not have privileges to access to the database.

Usually, to fix the problem, you only need to grant privileges to the login. If you grant sysadmin privileges to the user, the user will have all the possible privileges in all the databases and the SQL Server.

For a list of server level roles and the privileges, refer to this link: Server-Level Roles

Granting sysadmin privileges

In SSMS, go to Security>Logins and right click the login that you want to assign the sysadmin role:

Go to server roles and check sysadmin. This option will convert your login to a system administrator with all the privileges in SQL Server.

If you prefer the command line, you can use the following code:

ALTER SERVER ROLE [sysadmin] ADD MEMBER [peter]

GO

You can also assign database permissions to access to your data. At the database level, you also have Database role membership:

Here is the useful link to follow that explains each type of database roles explanation of each database roles: Database-Level Roles

A collation problem

The error in SQL Server could be also related to the collation which sometimes is NULL. To verify the collation, right click in SSMS on your database and select properties.

In the general page, you can see the collation:

Another way to see the collation is the T-SQL. The following example shows how to get the collation of the customer database:

SELECT

name,

collation_name

FROM sys.databases

where name=’customer’

You can also check by pressing F7 or go to the Menu in SSMS and select View and Object Explorer Details.

In Object Explorer Details, select databases and you will be able to see the Collation in one of the columns:

If you have this error, right click on columns and uncheck the collation. Refresh the icon and it should fix the problem.

If you believe that your database is corrupt or damaged and none of the alternatives worked, there is a software that you can download to repair SQL Server databases. The software name is Stellar Repair for MS SQL and you can read the software review from here.

Conclusion

In this article, we learned how to fix the error 916 in SQL Server 2008, 2012, 2014 2016 and the latest 2017 versions the problem is mainly related to permissions, but in some scenarios can be related to the collection equal to NULL.

Also, if your database is corrupted, you can take advantage of SQL repair software.

If you have questions, do not hesitate to write your comments