I setup a new user login and attached it to a specific database on my server. I then opened up management studio on my local pc, connected to the database using the server IP and selecting the database and everything worked great!

Then I went messing with the default SQL server Public server role as I wanted only this one database to be shown to the user - not the full list of all our databases on the server. I removed the "connect" option from TSQL Default TCP/TSQL Default VIA/TSQL Local Machine/TSQL Named Pipes. This obviously broke what I had so I tried to re-add these settings but now every time I open the properties tab on the Public server role I see a warning message telling me:

Value does not fall within the expected range (SqlMgmt)

If I try to connect to my database on my local pc, it will connect but just see "System Databases - master and tempdb" and "Database Snapshots" which is empty. I don't see the actual database I mapped the login to.

Is there anyway to either restore the Public role to the default settings or a way to get my database to to actually display in management studio when I connect.

Thanks, Rich

2 Answers 2


Well, this would be the script to restore the connection permissions to the defaults.

use [master]
GRANT CONNECT ON ENDPOINT::[TSQL Local Machine] TO [public] AS [sa]
GRANT CONNECT ON ENDPOINT::[TSQL Named Pipes] TO [public] AS [sa]

If that doesn't work, try stopping SQL Server, starting it in single user mode ("sqlservr.exe -m" from an administrator command prompt), connecting with sqlcmd.exe (which will default to the local default instance with Windows authentication), and running the script that way.


I finally got this working but had to run through the following hoops -

I noticed we were running SQL Server 2005 SP2. I upgraded to the latest service pack (4).

This didn't seem to solve my issue of being able to view the database when remotely logging on. So I tried something else:

I deleted the database and user.

I re-added the database and user but I did not assign the user to the database using the User Mappings option.

Instead I ran this sql script to attach the user as the database owner:

EXEC sp_changedbowner 'myuser'

This now allows me to remote access into the database.

To be honest, I'm not 100% sure why this worked but I'm glad it did!

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.