The SELECT permissions was denied on the object 'dm_os_host_info', database 'mssqlsystemresource, system

Vijay Kumar 2,016 Reputation points
2023-02-05T18:32:17.36+00:00

Hi Team,

Recently i am working on one test server, by mistakenly i revoked some permissions.

After that when i try to login i am getting error and i don't have any login account(Systadmin) to login to the server.

Please suggest me how to fix that.

I do have any account to login into the server to fix this issue by executing below command:

GRANT SELECT ON [sys].[dm_os_host_info] TO [public];

i tried with below command after putting SQL in single user mode but no use.

C:\Windows\system32>sqlcmd.exe -S MSSQLserver

Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user 'domain\userone'. Reason: Server is in single user mode. Only one administrator can connect at this time..

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,681 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 100.9K Reputation points MVP
    2023-02-05T19:12:58.75+00:00

    When you have lost sysadmin addess, starting SQL Server in single-user mode is a good start. Next step would be to run something like 

    SQLCMD -S MSSQLServer -Q"ALTER ROLE sysadmin ADD MEMEBER DOMAIN\YourUser"

    But since SQL Server is in single-user you have to be careful so that no one else takes that single connection. Set SQL Server Agent to Manual start. Make sure you don't have any Windows open in SSMS. If there is an application server, stop it, etc.

  2. Seeya Xi-MSFT 16,436 Reputation points
    2023-02-07T06:17:28.89+00:00

    Hi @vijay kumar ,

    I have stopped SQL Agent.

    Please refer to this similar thread: https://serverfault.com/questions/338290/cant-login-to-sql-server-in-single-user-mode

    1. Stop all SQL services
    2. Start with -m
    3. Run MSSQL Management Studio As administrator

    In addition, please read this blog: https://sqlserver-help.com/2013/12/06/help-how-to-fix-error-reason-server-is-in-single-user-mode-only-one-administrator-can-connect-at-this-time/

    Best regards,

    Seeya

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".