Deny connect to All databases for a login

Question

Deny connect to All databases for a login

WhiteLotus

SSChampion

Points: 11963
More actions
March 24, 2020 at 4:45 am

#3736494

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

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

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

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 1

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

ratbak SSC Eights! Points: 939 More actions · Answer 2

ratbak

SSC Eights!

Points: 939

March 24, 2020 at 1:16 pm

#3736579

DENY CONNECT ANY DATABASE TO [login]

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

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.
"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)
Intro to Tally Tables and Functions

John Mitchell-245523 SSC Guru Points: 148786 More actions · Answer 4

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: 1000539 More actions · Answer 5

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.
"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)
Intro to Tally Tables and Functions

Alejandro Santana SSCommitted Points: 1828 More actions · Answer 6

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?:(

This reply was modified 4 years, 6 months ago by Alejandro Santana.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

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.
"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)
Intro to Tally Tables and Functions

Alejandro Santana SSCommitted Points: 1828 More actions · Answer 8

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: 1000539 More actions · Answer 9

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.
"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)
Intro to Tally Tables and Functions

Alejandro Santana SSCommitted Points: 1828 More actions · Answer 10

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: 11963 More actions · Answer 11

Hi All,

Many thanks .

I decided to deny connect the account to SQL .

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 12

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.
"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)
Intro to Tally Tables and Functions