User without connect permission somehow have fullaccess to a user database!

Question

User without connect permission somehow have fullaccess to a user database!

howyue

SSC-Addicted

Points: 439
More actions
January 2, 2007 at 6:50 pm

#115672

I'm not a DBA, but a developer for sql 2005. I face the problem where everytime when i use the following statement to create a new login:
Create Login yy With Password = 'yy123Z'
i did not grant any permission and directly i use yy to login using sql management studio, guess what? yy able to connect to one of my application database with fullaccess. so i tried in my application database using my sa account:
Revoke Connect From yy
but it returns me error: Cannot find the user 'yy', because it does not exist or you do not have permission.
Please help! It is important that every newly created login CANNOT even have connect permission to my application database! i suspect if there's a place to set default permission for each newly created login... please suggest if i'm doing anything wrong! Thank q!

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

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

Erich Brinker SSCrazy Points: 2973 More actions · Answer 1

Is this login for a user that has administrative rights to the box? Sounds like you have a local admin accessing the database server through Builtin Admin rights to the SQL Server.

howyue SSC-Addicted Points: 439 More actions · Answer 2

Thank q for ur reply. i'm not quite understand what u said...

let me explain the situation more: the sql server is reside in the server, and i'm using my local pc to create the login (connect to the sql server using the sa account). I use my local pc to test login the yy account too.

i also check the [Builtin\Administrators] Login from sql server, and noticed that it has not mapped to any of the database. Same thing goes to [NT Authority\System].

i tried manually map the yy to my application database with rolemember 'Public', then yy is able to connect to the application database with only 'public' permission, meaning it can't see the user tables ... etc. I try the same thing on another sql server, and same thing, new user can access one of my application database too!

Please HELP!

Lowell SSC Guru Points: 323518 More actions · Answer 3

[Builtin\Administrators] would have the same rights as the sa user.....anyone in that group gets mapped to the dbo database owner, so it wouldn't be obvious that they have permissions, since it wouldn't explicitly say so.

the example you use for the username is not helpful....is it REALLY user YY, or could you be adding domainname\BobSomeguy, who also happens to be part of one of the Administrator groups?

also, even though you created the user YY, they might be connecting via managmenet studio as domainname\BobSomeguy, and thus bypassing your login altogether, and using whatever rights are assigned to theri network login.

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!

howyue SSC-Addicted Points: 439 More actions · Answer 4

Thank q for ur reply.

As u said SQL2k5 wouldn't explicitly indicate what [Builtin\Administrator] permission has, how do i check if my login belongs to which group?

the example " Create Login yy With Password = 'yy123Z' " is the actual statement i used to create the login, so i don think i was creating user that maps to a domain.

Yes. i was suspectin the same thing, although i login as yy, it looks like i am loggin in with different permission. When i connect using Management Studio, i manually type in the username yy and password. And for sure my server does not grant any permission to any domain user, in fact there is only a handful user inside.

is there anyway i can get rid of this situation? like i can check my permission or my exact logged in user when i login as yy? so that i can find out what actually is causing this?

Thank q!

Erich Brinker SSCrazy Points: 2973 More actions · Answer 5

If you right click on the Built IN Admin user in Mgmt Studio and select properties you should be able to click on the Server Roles tab and view what server role they are part of most likely it will show sysadmin

Anders Pedersen SSChampion Points: 11410 More actions · Answer 6

You could try the following to see if the login has serveradmin rights:

select

IS_SRVROLEMEMBER('serveradmin ')

Run this in a query window with the login you want to test. Look it up in booksonline for details.

Can also use

select SYSTEM_USER

to see who is logged in in the query window, this will give you the login used to connect to SQL.

select

user

will give you the user in the current database you are in. For me when logged in with my normal login this will show dbo since I am database owner.

Hope that helps a bit.

Lowell SSC Guru Points: 323518 More actions · Answer 7

you might also want get the user to connect, and use sp_who or sp_who2 adn see what username they connected with... that might help track down what login they use and how they get expanded permissions.

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!

howyue SSC-Addicted Points: 439 More actions · Answer 8

I tried login as yy into my application database and execute the checkin u guys suggested.

I check using IS_SRVROLEMEMBER against sysadmin,dbcreator,bulkadmin,diskadmin,processadmin, serveradmin,setupadmin,securityadmin and yy belongs to none of them, which is correct as i did not assign yy any server role when i create the login.

I check using SELECT USER also, it returns me 'guest' which i'm not very sure what does it mean?

And then the weirdest thing happen, when i check using IS_MEMBER('db_owner'), it returns me 1! I guess that is why i have fullaccess to my application database. I then quickly login as sa and double check on the security|login|yy|properties|User Mapping, NONE of the database is mapped! and i also ensure "Guest account enabled for: ApplicationDatabase" is unchecked.

Another thing, i execute SELECT SYSTEM_USER, it returns me yy.

I'm really out of any idea, hope u guys can help me, Please! Thank you.