1

My SSMS(SQL Server Management Studio) version 16.4, Windows 7. First install is normal use, sometimes i reinstalling my Windows and just now i install SSMS again, and got this problem. I cannot do anything except view.

Object Explorer & Registered Servers Display

Object Explorer & Registered Servers Display

  • BUILTIN\Users (ServerRole: Public) > using windows authentication
  • sa (ServerRole: Public, Sysadmin) > SQL Server Authentication (with default password)

I cannot create any database, restore or anything that change the content of my server. Also i have try to Add new login, and i don't have permission to create new login. I run the SSMS as Administrator, but i still cannot do anything.

Anyone know how to add new logins from out of application. Maybe i can connect to Server with SQL Server Authentication that have all permission that required. Or any way to solve this permission problem.

Thanks

Attach Database -> Add

Attach Database

Restore Database

Restore Database

Try something to do Try something to do

Error when add ServerRole to my BUILTIN\Users Error when add ServerRole to my <code>BUILTIN\Users</code>

Connection Properties if needed. Connection Properties if needed.

1

You are just a member of BUILTIN/Users when connecting to SQL Server, your USER\Administrator is not a member of sysadmin role, so you have no server permissions. Try to execute

select * 
from sys.fn_my_permissions(null, 'server');

and all you see will be CONNECT SQL + VIEW ANY DATABASE. Of course you cannot use DAC, you are NOT sysadmin to be able to do it.

What you should do instead is launch your server in single user mode, and as you are local admin, you'll be able to log in as sysadmin. After that you should add yourself to sysadmin role.

Here are the steps to perform: Connect to SQL Server When System Administrators Are Locked Out

-1

@Muhamad Sofyan, I have gone through your error log as it's showing the Microsoft SQL server , Error : 916.

Reasons for SQL Server Error 916

Here are some possible reasons for SQL Server Error 916:

 1. A login does not have permission to view data of a column selected
    in the header 

 2. A database is offline and/or its collation is NULL

 3. Multiple databases with different collations are on the instance and
    SSMS is unable to retrieve the collation because a database is
    configured to auto-close

For Correct SQL Server Error 916 follow the steps Here

Note: In your case the error comes from the database is in model database

  • Like you said, login does not have permission, the problem my BUILTIN/Users just have public permission. I follow the steps, and stuck when login with sa account. I browsing for a while to get the default password of sa account, and i don't get the right password to use. – Muhamad Sofyan Nov 12 '17 at 13:29
  • 1
    @Muhamad Sofyan, Did you try through DAC login. – Md Haidar Ali Khan Nov 12 '17 at 13:34
  • I am sorry, what is DAC? If windows account, I set only administration account on my windows. – Muhamad Sofyan Nov 12 '17 at 13:39
  • @MuhamadSofyan brentozar.com/go/dac – MguerraTorres Nov 12 '17 at 14:06
  • >>>Did you try through DAC login<<< To be able to login using DAC, one should be sysadmin, and he is NOT – sepupic Nov 13 '17 at 7:22
-4

query keyin

EXEC sp_addsrvrolemember 'Domain\User' 'sysadmin';

It works for me.

  • 1
    This answer would be much better if you explained how it addressed the problem, and how the OP can execute the query without a functioning SSMS. – Michael Green Aug 7 '18 at 3:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.