Find All SQL Servers On A Network with sqlcmd

In the event that anyone should ask for it ūüôā we are compiling a list of All SQL Servers, Databases, and Contacts for each application we support in our environment.

We have a central repository, that’s updated daily,¬†that houses that information.¬† That feed, however,¬†and any documentation we create, is only as good as it is current.¬† We are human…¬†and need to periodically review systems¬†to ensure that we collect any that may have slipped through the cracks.¬† It’s also not a bad idea to see what’s out there that you may have forgotten about or were’nt even aware of…

Either the osql¬†or sqlcmd¬†utilities can be used to list all SQL Instances with the following syntax –

osql /L

sqlcmd -L

While both osql and sqlcmd are currently supported, it’s worth pointing out that the sqlcmd utility, was incorporated into SQL 2005, is more feature rich, and should be used going forward.

Advertisements

Grant User the Right to View Database Schema

While we normally frown upon granting backend access to our Production SQL Servers we have, on occasion, had a need to allow users to veiw the schema for objects within our Production databases. 

 

Example 1:¬† We have a “reporting” database that is refreshed nightly from a copy of our Production DB.¬† Report writers need to be able to veiw the table definitions to know which fields are available to them to build their reports.¬†

 

Example 2:¬† Errors were being generated in our production environment and they were¬†believed to be related to improper versions of a couple of stored procedures¬†having been released.¬† In order to do a schema comparison, quickly,¬†between our development¬†and production environments, to ensure all differences were corrected, we temporarily granted a developer “View Definition” access to allow them to perform a schema comparison.

 

Use the statement below to Grant User the Right to View Database Schema –

 

GRANT VIEW DEFINITION ON DATABASE::DBName TO UserName/RoleName

 

It’s a great way to allow the necessary access without having to grant elevated priveleges.