Can I restore the defult SQL Server Public Role?

Question

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

Answer 1

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

use [master]
GO
GRANT CONNECT ON ENDPOINT::[TSQL Default TCP] TO [public] AS [sa]
GRANT CONNECT ON ENDPOINT::[TSQL Default VIA] TO [public] AS [sa]
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.

Answer 2

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!