locked
Limiting access to an instance through SSMS RRS feed

  • Question

  • Hi, I need some advice on how to approach a security/data integrity concern.  Several of our developers need to access SQL instances in both the LAB and production.  Right now they want the direct access to the production instance restricted because they don't want to access the instance directly from SSMS.  Their concern is that it's too easy to forget which instance your connected to when your connecting to them all via SSMS and folks could inadvertently do destructive things on production.  Right now the solution is that they have to RDP to the production server to make SQL changes.

    This solution works but obviously it's not ideal or scalable as the server runs more instances, etc.  What would be a recommended way to approach this?  My initial idea was just to have tiered credentials and you have to logon with the elevated user to make changes.  They still don't like this because SSMS caches these credentials and they'll automatically be used the next time they connect to the instance.

    On a related note, how can you connect to an instance using alternate windows connections?  When you hit an instance via SSMS using windows auth it automatically picks up your logon credentials.  How can I use different ones? 

    Thank you.

    Saturday, April 14, 2012 3:37 PM

Answers

  • There a couple of ways to mitigate this problem.

    To start with, under Tools->Options->Text Editor->Editor Tab and Status Bar, you can control what is displayed on the tabs. This permits you to remove unnecessary clutter like the filename. Well, at least I think that's clutter, since I only use SSMS for ad hoc queries. I realise that this is not what you are asking for, but I still mention this option.

    As I understand, you RDP to the production server itself. An alternative is to RDP to a decicated terminal server. Yet an alternative is to simply have an virtual machine and run SSMS from this machine. You could be logged on with different credentials in the VM.

    Yet a solution is to use SQL Server authentication in one of the environments, but that may not be desireable.

    If you want to log in from SSMS with a different Windows user, this is possible with the Runas command in Windows. Run "runas /?" from the command prompt for more details.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by ITandStuffX2 Monday, April 16, 2012 2:48 PM
    Saturday, April 14, 2012 5:06 PM

All replies

  • There a couple of ways to mitigate this problem.

    To start with, under Tools->Options->Text Editor->Editor Tab and Status Bar, you can control what is displayed on the tabs. This permits you to remove unnecessary clutter like the filename. Well, at least I think that's clutter, since I only use SSMS for ad hoc queries. I realise that this is not what you are asking for, but I still mention this option.

    As I understand, you RDP to the production server itself. An alternative is to RDP to a decicated terminal server. Yet an alternative is to simply have an virtual machine and run SSMS from this machine. You could be logged on with different credentials in the VM.

    Yet a solution is to use SQL Server authentication in one of the environments, but that may not be desireable.

    If you want to log in from SSMS with a different Windows user, this is possible with the Runas command in Windows. Run "runas /?" from the command prompt for more details.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by ITandStuffX2 Monday, April 16, 2012 2:48 PM
    Saturday, April 14, 2012 5:06 PM
  • However, this works perfectly if the user is the owner of the database. Look 
    that …

    • Create a new SQL login "login1"
    • Create a user named “login1” in master database
    • Grant CREATE DATABASE to login1
    • While impersonating login1, create a database called “dbteste”
    • Revoke CREATE DATABASE permission from login1
    • Revoke VIEW ANY DATABASE permission from PUBLIC
    • Register this server as login1
    • From the “login1” session, expand database tree. Now, you should see 
    master, tempdb, dbteste
    • Grant VIEW ANY DATABASE to PUBLIC
    • From the “login1” session, you should see all the databases

    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Sunday, April 15, 2012 11:14 AM
  • Thanks for all the ideas.  Definitely some good ones that will be useful.
    Monday, April 16, 2012 2:48 PM