sp_defaultdb and other procs available to public?

Question

sp_defaultdb and other procs available to public?

Jared

SSC Guru

Points: 61793
More actions
February 21, 2014 at 1:08 pm

#283824

We have just recently noticed that a member of a group who executes sp_defaultdb will have his account added as an individual login with public rights. Our concern, here, is that when we remove this person from the group and assume he/she no longer has access, we would be incorrect.
This is just one example of a system proc where users with public access can execute it and bypass some security measures. Does anyone have a list of others that may be good to revoke permissions from public?
Jared
CE - Microsoft

Viewing 8 posts - 1 through 7 (of 7 total)

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

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 1

SQLKnowItAll (2/21/2014)
We have just recently noticed that a member of a group who executes sp_defaultdb will have his account added as an individual login with public rights. Our concern, here, is that when we remove this person from the group and assume he/she no longer has access, we would be incorrect.
This is just one example of a system proc where users with public access can execute it and bypass some security measures. Does anyone have a list of others that may be good to revoke permissions from public?

I guess I wouldn't blame the security issue on sp_defaultdb because that proc requires the "ALTER ANY LOGIN" to be executed. That's not exactly a PUBLIC priv.

The real problem is in the groups that have been created. I'd suggest a review of the privs given in groups.

--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)

Jared SSC Guru Points: 61793 More actions · Answer 2

Jeff Moden (2/21/2014)
SQLKnowItAll (2/21/2014)
We have just recently noticed that a member of a group who executes sp_defaultdb will have his account added as an individual login with public rights. Our concern, here, is that when we remove this person from the group and assume he/she no longer has access, we would be incorrect.
This is just one example of a system proc where users with public access can execute it and bypass some security measures. Does anyone have a list of others that may be good to revoke permissions from public?
I guess I wouldn't blame the security issue on sp_defaultdb because that proc requires the "ALTER ANY LOGIN" to be executed. That's not exactly a PUBLIC priv.
The real problem is in the groups that have been created. I'd suggest a review of the privs given in groups.

Are you sure about that? 🙂 The group has only Public privileges... Verified. Public has no Login privileges... Verified... Where else could I look? I see what you are saying, but I see something different happening...

Jared
CE - Microsoft

Lowell SSC Guru Points: 323518 More actions · Answer 3

while a user might be able to see system procedures, they can't do anything with them due to permissions;

it's easy to test, just create a user.

create user ClarkKent without login

execute as user='ClarkKent'

select * from sys.procedures --nothing to see

select * from sys.all_objects --lots of system stuff, but they don't let

exec sp_defaultdb 'ClarkKent','master'

Msg 15007, Level 16, State 1, Procedure sp_defaultdb, Line 41
'ClarkKent' is not a valid login or you do not have permission.

so if you have a user than can call something, you should check his roles to see what permissions he's inheriting.

was this a windows user?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jared SSC Guru Points: 61793 More actions · Answer 4

Lowell (2/22/2014)
while a user might be able to see system procedures, they can't do anything with them due to permissions;
it's easy to test, just create a user.
create user ClarkKent without login
execute as user='ClarkKent'
select * from sys.procedures --nothing to see
select * from sys.all_objects --lots of system stuff, but they don't let
exec sp_defaultdb 'ClarkKent','master'
Msg 15007, Level 16, State 1, Procedure sp_defaultdb, Line 41
'ClarkKent' is not a valid login or you do not have permission.
so if you have a user than can call something, you should check his roles to see what permissions he's inheriting.
was this a windows user?

Yes. A windows user within a Windows group. I'll take a look on Monday and see if there is something I am missing. I suppose there has to be! 😉

Jared
CE - Microsoft

Jared SSC Guru Points: 61793 More actions · Answer 5

Yeah, I am looking really hard to find any inherited permissions for this, but I cannot. Again, the scenario is that a Windows Group is added with default, public, permissions. Then as a member of that group, I log in and execute sp_defaultdb 'DOMAIN\mylogin', 'dba_database' and it completes successfully and also I can view the login.

Jared
CE - Microsoft

Jared SSC Guru Points: 61793 More actions · Answer 6

Hmm... This works just fine...

CREATE login ClarkKent WITH password = 'P@$$w0rdSr0ngEnough'

execute as login='ClarkKent'

select * from sys.procedures --nothing to see

select * from sys.all_objects --lots of system stuff, but they ALSO let

exec sp_defaultdb 'ClarkKent','master'--or any other database

--REVERT

Thoughts?

Jared
CE - Microsoft

Jared SSC Guru Points: 61793 More actions · Answer 7

I want to bump this up again, as I have proved that an active directory user that is part of an active directory group that has only public permissions can run the below statement and their individual account will be added as a login with public privileges.

Exec sp_defaultdb @loginame='domain\username', @defdb='foo'--where domain\username is a member of an AD group with public permissions

Thoughts?

Jared
CE - Microsoft