Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

migrated from serverfault.com Jan 19 '12 at 14:47

This question came from our site for system and network administrators.

2 Answers 2

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.

share|improve this answer
    
Thanks for your comment db2. I tried the above but it didn't work for me. As per my answer above, I had to install the latest service pack and delete/re-add the database and user to get this working again. –  Richard Reddy Jan 23 '12 at 14:45
up vote 0 down vote accepted

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!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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