Deny connect to All databases for a login

  • WhiteLotus

    SSChampion

    Points: 11930

    Hi All

    I would like to deny connect to all databases for a new login using T-SQL.

    Anyone knows how to achieve this ?

    I only want to know using T-SQL/ SQL command.

    Thank you

     

     

     

  • John Mitchell-245523

    SSC Guru

    Points: 148671

    Just revoke all permissions on all databases?  And make sure that it doesn't have any server-level access (eg sysadmin) that overrides that.

    John

  • rick.thorington

    SSC Rookie

    Points: 35

    DENY CONNECT ANY DATABASE TO [login]

  • Jeff Moden

    SSC Guru

    Points: 996046

    Why wouldn't you just disable the login?

     

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • John Mitchell-245523

    SSC Guru

    Points: 148671

    Jeff Moden wrote:

    Why wouldn't you just disable the login?

    Might still need server-level access, for example to create logins or linked servers.

    John

  • Jeff Moden

    SSC Guru

    Points: 996046

    John Mitchell-245523 wrote:

    Jeff Moden wrote:

    Why wouldn't you just disable the login?

    Might still need server-level access, for example to create logins or linked servers.

    John

    If that's true, I wish the OP would state the reason for the requirement so that we don't need to guess. 🙁

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Alejandro Santana

    SSCommitted

    Points: 1777

    Jeff Moden wrote:

    John Mitchell-245523 wrote:

    Jeff Moden wrote:

    Why wouldn't you just disable the login?

    Might still need server-level access, for example to create logins or linked servers.

    John

    If that's true, I wish the OP would state the reason for the requirement so that we don't need to guess. 🙁

     

    Also i think for linked server creation you must be sysadmin...

    Microsoft when we will have this corrected?:(

  • Jeff Moden

    SSC Guru

    Points: 996046

    I actually hope that MS doesn't ever fix that.  As the resident DBA for my company, I don't want a bunch of yahoooos building linked servers that could cause breaks in security.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Alejandro Santana

    SSCommitted

    Points: 1777

    They should add something like "Create", "modify" and "Drop" linked servers, i think that should be the way to go.

    In our case, we had to give it to the IT sec team and there isn't a permission for it.. we had to give them the sysadmin role (i know this is the biggest mistake ever) their job basically is manage Linked Servers (because of their nature, connecting to others engines) and create users and add permissions.... we have a bunch of stuff for audit and triggers to stop drop tables and drop databases but that still makes me uncomfortable, audits also for DBCC commands, alters inside databases. etc etc. but still i think they have way too much power, and they are not even DBAs.

    but i'm just the junior DBA so my opinion is not that important  ¯\_(?)_/¯

  • Jeff Moden

    SSC Guru

    Points: 996046

    I agree with you.  That's too much power for what they do.  And they shouldn't be creating linked servers for just anyone.

    However, if that's the way it is and you can't convince the Senior DBAs that all of that might be a career changing move (from DBA to washing dishes), you still don't need to give them squat for privs except the privs to run a stored procedure (that you need to write) that will create a Linked Server.  Done correctly, the proc would have the privs... not the yahooos. 😀  Let me know if you need any help there.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Alejandro Santana

    SSCommitted

    Points: 1777

    That sounds REALLY interesting, i have never been the type of person and i didn't knew you could make these kind of changes inside the engine if not using MSFT's SP that were designed to make the task. is there some sort of link? or its actually more like experience through the time or it is documented somewhere? :o, maybe i'm just scared of making changes to these tables and screwing everything up.

    I'm gonna give it a try and you let me know if i failed hahaha,

    here it goes: look up all the system tables that the actual SP's that create/edit the link server touches and make the changes directly onto these tables. (If that's how it actually works i wouldn't be surprised) but me, as an end user of the SQL Engine wouldn't have crossed my mind to touch these tables directly (id be sh#t scared to touch these tables) if not using their SPs.

    Thanks!

     

  • WhiteLotus

    SSChampion

    Points: 11930

    Hi All,

    Many thanks .

    I decided to deny connect the account to SQL .

     

     

  • Jeff Moden

    SSC Guru

    Points: 996046

    Alejandro Santana wrote:

    That sounds REALLY interesting, i have never been the type of person and i didn't knew you could make these kind of changes inside the engine if not using MSFT's SP that were designed to make the task. is there some sort of link? or its actually more like experience through the time or it is documented somewhere? :o, maybe i'm just scared of making changes to these tables and screwing everything up.

    I'm gonna give it a try and you let me know if i failed hahaha,

    here it goes: look up all the system tables that the actual SP's that create/edit the link server touches and make the changes directly onto these tables. (If that's how it actually works i wouldn't be surprised) but me, as an end user of the SQL Engine wouldn't have crossed my mind to touch these tables directly (id be sh#t scared to touch these tables) if not using their SPs.

    Thanks!

    I actually have a script that does it all.   It could easily be made into a stored procedure.  And, no... it doesn't insert directly into any tables... it uses system stored procedures to do it.  I'll write it up with instructions for you tonight after work.  It's actually pretty easy if you have followed what most people consider to be the "Best Practice" for the owner of the database.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply