Skip to content

SQL Server DBA - Dedicated Blog for all DBA and Developers

SQL DBA blog is dedicated in providing the highest quality and most in-depth SQL Server material to boost your career or to make one more productive.
  • Home
  • SQL Admin
    • SQL Development
    • SQL Server Basics
      • SQL Server Architecture
      • SQL Server Management Studio
      • SQL Server Configuration Manager
    • Using Query Analyser
    • Adding SQL Agent Job
    • Backing up SQL Server Database
    • Restoring Database in SQL Server
    • SQL Server Activity Monitor
    • Using SQL Server Database Tuning Advisor
    • Using SQL Server Profiler
    • SQL Server RPO and RTO
    • SQL Server 2008 HA and DR Solutions
    • SQL Server Encryption
  • High Availability
    • Failover Clustering
    • Database Mirroring
    • Replication
      • Transactional Replication
      • Snapshot Replication
      • Merge Replication
    • Disaster Recovery
      • Backup and Restore
      • Log Shipping
      • Database Mirroring
  • Performance Tuning
    • Operating System Memory Counters
      • OS Memory and Paging Performance Counters
      • OS CPU and Processor Counters
      • OS Disk and Miscellaneous Counters
    • SQL Server Memory
      • Buffer Manager & Memory Performance Counters
      • SQL Server: Memory Manager Counters
      • My Memory Usage– Performance Counters
    • Other Important Performance Counters
      • Data Access Performance Counters
      • User Database Performance Counters
      • SQL Server SQL Statistics Counters
      • Workload Performance Counters
      • Important Perfmon Counters %Processor and Disk monitoring counters
      • SQL Server : Plan Cache Counters
    • Users & Locks Performance Counters
    • Getting Perfmon data from inside of SSMS
  • BI Tools
    • Administration of Reporting Services
    • Administration of Analysis Services
    • Scheduling Integration Services Package
  • Contact Us

SQL Server DBA - Dedicated Blog for all DBA and Developers

SQL DBA blog is dedicated in providing the highest quality and most in-depth SQL Server material to boost your career or to make one more productive.
  • Home
  • SQL Admin
    • SQL Development
    • SQL Server Basics
      • SQL Server Architecture
      • SQL Server Management Studio
      • SQL Server Configuration Manager
    • Using Query Analyser
    • Adding SQL Agent Job
    • Backing up SQL Server Database
    • Restoring Database in SQL Server
    • SQL Server Activity Monitor
    • Using SQL Server Database Tuning Advisor
    • Using SQL Server Profiler
    • SQL Server RPO and RTO
    • SQL Server 2008 HA and DR Solutions
    • SQL Server Encryption
  • High Availability
    • Failover Clustering
    • Database Mirroring
    • Replication
      • Transactional Replication
      • Snapshot Replication
      • Merge Replication
    • Disaster Recovery
      • Backup and Restore
      • Log Shipping
      • Database Mirroring
  • Performance Tuning
    • Operating System Memory Counters
      • OS Memory and Paging Performance Counters
      • OS CPU and Processor Counters
      • OS Disk and Miscellaneous Counters
    • SQL Server Memory
      • Buffer Manager & Memory Performance Counters
      • SQL Server: Memory Manager Counters
      • My Memory Usage– Performance Counters
    • Other Important Performance Counters
      • Data Access Performance Counters
      • User Database Performance Counters
      • SQL Server SQL Statistics Counters
      • Workload Performance Counters
      • Important Perfmon Counters %Processor and Disk monitoring counters
      • SQL Server : Plan Cache Counters
    • Users & Locks Performance Counters
    • Getting Perfmon data from inside of SSMS
  • BI Tools
    • Administration of Reporting Services
    • Administration of Analysis Services
    • Scheduling Integration Services Package
  • Contact Us
Home » SQL Server Error : 916, Severity: 14. The server principal “%.*ls” is not able

SQL Server Error : 916, Severity: 14. The server principal “%.*ls” is not able

  • Vamshi B Vamshi B
  • October 2, 2021
  • SQL Server

Table of Contents

  • SQL Server Error : 916 Details
  • Reason or Cause of error
  • Reading sql server error log location from SQL Query
  • Solution for Resolving the Error
  • Alternate Solutions
    • Restarting SQL Server Service(non production instances only)
      • Other ways for restarting SQL server Service
    • 2.Checking SQL Performance metrics like CPU, Memory
  • SQL Server Error Code and solution summary

SQL Server Error : 916 Details


SQL Server Error: 916
Severity: 14
Event Logged or not: No
Description:
The server principal “%.*ls” is not able to access the database “%.*ls” under the current security context.
Severity 14 Description:
Indicates security-related errors, such as permission denied.

Accessing the SQL server database using SQL Server Management Studio with a limited number of permissions (database read or write) may result in SQL Server Error 916, with the message “The server principal username is unable to access the database database name under the current security context.”

Reason or Cause of error

The login lacks the necessary permissions to connect to the specified database. Logins that can connect to this instance of SQL Server but do not have specific permissions in a database are granted the guest user’s permissions. This is a security measure that prevents users in one database from connecting to databases in which they do not have access. This error message may appear if the guest user lacks CONNECT permission to the named database and the trustworthy property is not set. This error message may appear if the guest user lacks CONNECT permission to the specified database.

The main issue is caused by SQL Server Management Studio bugs that prevent the user from connecting to the database and refuse to display the database list. There could be a number of other causes for SQL Server Error 916.
The user is not allowed to view the data of the selected column in the database. The database is not currently accessible.
There are several columns in the list of Object Explorer Details, including Size, Space available, Data Space Used, Default file group, Index Space Used, Mail host, and Primary file path, and the user has added at least one of them.
If the database’s Auto Close option is enabled, SQL SSMS cannot retrieve the Collation column.

 

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

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

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

Cannot show requested dialog.(SqlMgmt)

Microsoft Office Communications Server 2007 (OCS 2007) can be affected by this issue and the services will not start even after the server is started. The following error message will be visible 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’

 

Reading sql server error log location from SQL Query

Identifying SQL Server Error Log File used by SQL Server Database Engine can be done by reading SQL Server Error Logs. DBA can execute the XP_READERRORLOG extended stored procedure to read the SQL Server Error Log and search for its location used by the instance of SQL Server.

USE master
Go
xp_readerrorlog 0, 1, N'Logging SQL Server messages in file', NULL, NULL, N'asc'
Go

The parameters for XP_READERRRORLOG are:
1. Value of error log file we would like to read. values are 0 = current, 1 = last one before current, 2 = second last before current etc…
2. Log file type:- 1 or NULL = error log, 2 = SQL Agent log
3. Search string 1:- String one you want to search for
4. Search string 2:- String two you want to search for to further refine the results
5. start time for Search
6. end time for search
7. Sort order for search results:- N’asc’ = ascending, N’desc’ = descending

By default, we have 6 Server Error Logs kept but we can increase the number of SQL Server Error Logs from the default value of six.

For other ways to read and find error log location please our artcile https://sqlserver-dba.co.uk/error-log/sql-server-identify-location-of-the-sql-server-error-log-file.html

Solution for Resolving the Error

Grant permissions:

use msdb  ;

GO

GRANT CONNECT TO [Adventure-Works\Test] ;

USE msdb ;

GO

GRANT CONNECT TO guest ;

and

ALTER DATABASE AdventureWorks SET TRUSTWORTHY ON;

We can check the granted permissions for the guest-user by using the following command (sysadmin fixed server member should run this)

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

When the above command is executed, the user is presented with a table containing all of the guest user’s attributes.

An empty result, on the other hand, indicates that the guest user has been disabled in the database, and the SQL Server error 916 will be displayed once more. You can correct the error by implementing one of the available solutions.

1. The user can use the following solutions to resolve the error when accessing the database. Any of them will assist you.

  • In the SSMS, go to the View section and select Object Explorer Details.
  • Deselect Collation by right-clicking on Column Header.
  • Refresh the server and operate a database.

2. From the SSMS menu, select View > Object Explorer details.
Click the Database folder in the Object Explorer window.
Select Reset View from the menu that appears when you right-click on the column header.
Refresh the Database folder to complete the final step.

3. Check the database’s Auto Close setting, set it to False, and continue.
The solutions described above may resolve the issue, allowing the user to continue with the workflow.

 

Alternate Solutions

  1. Restarting SQL Server Service(non production instances only)

  • To Restart, Start or Stop the SQL Server instance by right click on sql server instance in SSMS or in SQL. You may need to open SSMS as administrator to start, stop the instance.

db-mail4

  • Other ways for restarting SQL server Service

  1. From SQL Configuration manager from Start menu
  2. From Services in Windows server
  3. From Cmd using net start and net stop

2.Checking SQL Performance metrics like CPU, Memory

Check SQL Server CPU, Memory usage, longest running queries, deadlocks etc.. using activity monitor or sp_who2.

To view Activity Monitor in SQL Server 2005 and in SQL Server 2008, a user must have VIEW SERVER STATE permission.

2 Different Ways to Open up Activity Monitor in SQL Server 2008 are mentioned below:

Open up Activity Monitor Using Object Explorer

In Object Explorer, right click the SQL Server 2008 Instance and click on Activity Monitor.

Also can be opened from SQL Server 2008 Management Studio’s toolbar, by clicking Activity Monitor

Opening SQL Server Activity Monitor method2

 

SQL Server Error Code and solution summary


SQL Server Error: 916
Severity: 14
Event Logged or not: No
Description:
The server principal “%.*ls” is not able to access the database “%.*ls” under the current security context.

 

With the help of this page, a person can learn about SQL Server error 916, the problems that occur as a result of it, and the causes. As a result, a user can obtain detailed information about the SQL server as well as the possible error message displayed if the guest user wishes to expand the database node in order to view its data.

Tags:916 sql server errorerror 916 in sql servererror 916 sql servererror 916 sql server 2005error 916 sql server azuremicrosoft sql server error 916microsoft sql server error 916 azuresql 916 errorsql error 916 security contextsql server 2005 error 916sql server 2008 error 916sql server error 916sql server error 916 azuresql server error 926sql server error code 916sql server login error 916sql server sqlstate 08004 (error 916)

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Categories

  • Backup and Restore
  • Concurrency Control
  • Connectivity Issues
  • CTE
  • Database Mail
  • Database Maintenance
  • Database Mirrroring
  • Dealocks
  • Disaster Recovery
  • Disk I/O
  • Error Log
  • Extended Events
  • FTP
  • High Availability
  • In-Memory OLTP
  • Indexes
  • Log Shipping
  • MDW
  • Memory Configurations
  • New Features
  • Performance issues
  • Performance Tuning
  • Plan Cache
  • Policy Based Management
  • PRINCE2
  • Project Management
  • Replication
  • Reporting Services
  • Resource Database
  • Resource Governor
  • Revolution Analytics
  • rto rpo
  • SQL Server
  • SQL Server Admin
  • SQL Server Administration
  • SQL Server Alerts and Notifications
  • SQL Server Architecture
  • SQL Server Errors
  • SQL Server Extended Events
  • SQL Server Indexes
  • SQL Server Locks
  • SQL Server on Linux
  • SQL Server Security
  • SSRS
  • Storage Internals
  • Stored Procedures
  • System Databases
  • Table Variable
  • TDE
  • Temporary Database
  • Temporary Tables
  • Temporary Variables
  • Transaction Log
  • Transaction Log Shipping
  • Transactional Replication
  • TSQL
  • Uncategorized
  • Wait Stats

Please follow & like us :)

RSS
Follow by Email
Facebook
fb-share-icon

My Cool Posts…

  • SQL Server Error : 10033, Severity: 16. The specified index does not exist or th
  • SQL Server Error : 10032, Severity: 16. Cannot return multiple result sets (not
  • SQL Server Error : 16389, Communication link failure
  • SQL Server Error : 1003, Severity: 15. Line %d: %ls clause allowed only for %ls
  • SQL Server Error : 10035, Severity: 16. No value was given for one or more of th
  • SQL Server Error : 10034, Severity: 16. The specified table or view does not exi
  • SQL Server Error : 16943, Severity: 16. Could not complete cursor operation beca
  • SQL Server Error : 16915, Severity: 16. A cursor with the name ‘%.*ls’ already e
  • SQL Server Error : 1639 The eror occurs during installation of SQL Server
  • SQL Server Error : 15404, Severity: 16. Could not obtain information about Windo

DBA

SQL Server DBA

Google site link is https://sites.google.com/view/sqlserver-dba

For list of all URL's https://docs.google.com/spreadsheets/d/1Pmt8rt4TOvvf8IXjudtqz_c-sLQQilRALQStYyVMK3I/edit?usp=sharing

Twitter Account is https://twitter.com/VamshidharBokka

Facebook page is www.facebook.com/SQLServerDBABlog

SQLServerDBA | Copyright by SQLServerDBA

We use cookies on our website to give you the most relevant experience by remembering your preferences and repeat visits. By clicking “Accept All”, you consent to the use of ALL the cookies. However, you may visit "Cookie Settings" to provide a controlled consent.
Cookie SettingsAccept All
Manage consent

Privacy Overview

This website uses cookies to improve your experience while you navigate through the website. Out of these, the cookies that are categorized as necessary are stored on your browser as they are essential for the working of basic functionalities of the website. We also use third-party cookies that help us analyze and understand how you use this website. These cookies will be stored in your browser only with your consent. You also have the option to opt-out of these cookies. But opting out of some of these cookies may affect your browsing experience.
Necessary
Always Enabled
Necessary cookies are absolutely essential for the website to function properly. These cookies ensure basic functionalities and security features of the website, anonymously.
CookieDurationDescription
cookielawinfo-checkbox-analytics11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Analytics".
cookielawinfo-checkbox-functional11 monthsThe cookie is set by GDPR cookie consent to record the user consent for the cookies in the category "Functional".
cookielawinfo-checkbox-necessary11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookies is used to store the user consent for the cookies in the category "Necessary".
cookielawinfo-checkbox-others11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Other.
cookielawinfo-checkbox-performance11 monthsThis cookie is set by GDPR Cookie Consent plugin. The cookie is used to store the user consent for the cookies in the category "Performance".
viewed_cookie_policy11 monthsThe cookie is set by the GDPR Cookie Consent plugin and is used to store whether or not user has consented to the use of cookies. It does not store any personal data.
Functional
Functional cookies help to perform certain functionalities like sharing the content of the website on social media platforms, collect feedbacks, and other third-party features.
Performance
Performance cookies are used to understand and analyze the key performance indexes of the website which helps in delivering a better user experience for the visitors.
Analytics
Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics the number of visitors, bounce rate, traffic source, etc.
Advertisement
Advertisement cookies are used to provide visitors with relevant ads and marketing campaigns. These cookies track visitors across websites and collect information to provide customized ads.
Others
Other uncategorized cookies are those that are being analyzed and have not been classified into a category as yet.
SAVE & ACCEPT