howyue


I tried asking the same question in other forum but couldn't get an answer.

I used exactly the following SQL to create a login with sa account in SQL2k5 Management Studio:
Create Login yy With Password='yy123Z'

Without granting any permission, straight away, i log out sa and login as yy in management studio, yy somehow can access one of my ApplicationDatabase! So i check with the following SQL in my ApplicationDatabase:
IS_MEMBER('db_owner')
and it returns me 1, meaning yy is db_owner. 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.

What could be the problem i am facing the purpose i create yy is to ONLY allow it to have connect permission on my SQL Endpoint. Please HELP!!




Re: Login without Connect Permission somehow managed to become db_owner!?

Madhu K Nair


It is strange... have u run this Create login statement from QA or was it ran from your customized frontend application for access control. I tried all the way, but i could not re-create your problem.

also post the reslut of

sp_helplogins 'yy'

sp_helpuser 'yy'

Madhu







Re: Login without Connect Permission somehow managed to become db_owner!?

Laurentiu Cristofor

You can also use the following two queries to determine the role memberships and the permissions associated with login yy:

select suser_name(role_principal_id), suser_name(member_principal_id) from sys.server_role_members where member_principal_id = suser_id('yy')
select permission_name, class_desc, major_id from sys.server_permissions where grantee_principal_id = suser_id('yy')

Thanks
Laurentiu







Re: Login without Connect Permission somehow managed to become db_owner!?

howyue

Thank q for the reply, i was handling another project for the past few weeks, now i get back to this problem.

Madhu,

i tried sp_helplogins 'yy' and sp_helpuser 'yy', i get "The name supplied (yy) is not a user, role, or aliased login." for sp_helpuser 'yy'. And i get the following for sp_helplogins 'yy':

LoginName SID DefDBName DefLangName AUser ARemote
---------------- ----------------------------------------------------------------- ----------------- --------------------- ---------- -------------
yy 0x470F4382CEBEA24EB0503FD835B63A59 master us_english NO no

and the 2nd result set is empty.

Laurentiu,

I tried ur sql too. the first query returns no record; the 2nd query returns me 1 record indicating'yy' has connect permission.

After running the queries, i find myself in even worse situation as the result proves to me that nothing is wrong with 'yy', but still somehow i can connect to the application db. by the way, i tried the same thing in another pc, same thing happened. i'm really lost, hope somebody can help me, thank q





Re: Login without Connect Permission somehow managed to become db_owner!?

Madhu K Nair

Are you connecting to server using this Login from your application If that is the case, somewhere in the code the loging must have hardcoded. open a query analyser using this login and try to connect to the database.

Madhu






Re: Login without Connect Permission somehow managed to become db_owner!?

Laurentiu Cristofor

Check the context you are logged in:

select suser_name()

and also check who owns the database by querying sys.databases:

select name, suser_sname(owner_sid) from sys.databases

Thanks
Laurentiu






Re: Login without Connect Permission somehow managed to become db_owner!?

howyue

Madhu,

Nope. i've been using query analyzer (SQL server studio management 2005) to connect all the while .. so far nothing to do with my application coding.

Laurentiu,

sorry that i really hav limited knowledge in sql security, i login as yy in query analyzer and execute:

Use ApplicationDB
GO
select suser_name()

Result: yy

And also i run the following:
select name, suser_sname(owner_sid) from sys.databases

Result: indicating my applicationDB is own by domain\Administrator

I'm not sure if there's something to do with my computer. is it possible that everytime i connect to the db, it uses my computer authentication instead of the yy





Re: Login without Connect Permission somehow managed to become db_owner!?

Laurentiu Cristofor

OK, so the queries you ran confirm that you are connected as 'yy' and that the database is not owned by 'yy', but by a Windows login. Were you still getting db_owner privileges when connecting to the database

I don't think there is anything wrong with your computer, but someone may have set up security in an incorrect way in your database server. I have one more query for you to try out:

select permission_name, class_desc, major_id from sys.server_permissions where grantee_principal_id = suser_id('public')

This can tell us what permissions are granted to the public server role - these permissions will apply to all users, including 'yy'.

Thanks
Laurentiu






Re: Login without Connect Permission somehow managed to become db_owner!?

howyue

Laurentiu,

Thank q for ur continuous replies.
I'm not sure if i'm gettin db_owner if i'm using windows login as i'm not able to connect the query analzer using windows logins.
select permission_name, class_desc, major_id from sys.server_permissions where grantee_principal_id = suser_id('public')

Returns me:

permission_name class_desc major_id
----------------------------------- ---------------- ------------
VIEW ANY DATABASE SERVER 0
CONNECT
ENDPOINT 2
CONNECT
ENDPOINT 3
CONNECT
ENDPOINT 4
CONNECT
ENDPOINT 5

this is interesting, "view any database" means yy will hav permission to view all database BUT, in fact there's 2 application database, and yy manage to view only ONE of it.





Re: Login without Connect Permission somehow managed to become db_owner!?

Tom Phillips

See BOL under of DB Roles

Every database user belongs to the public database role. When a user has not been granted or denied specific permissions on a securable, the user inherits the permissions granted to public on that securable.



You must have changed your "public" role on the 2nd database, which the user does not see.







Re: Login without Connect Permission somehow managed to become db_owner!?

Laurentiu Cristofor

The result of the query is normal. VIEW ANY DATABASE should allow yy to see an entry for each database if he selects from sys.databases. Is this not the case

Thanks
Laurentiu

PS: I'm checking the forum less often then I used to, so I won't be able to answer immediately, but I'll eventually answer.






Re: Login without Connect Permission somehow managed to become db_owner!?

howyue

Tom,

i have compare my public role between the accessible application db and other none accessible db, i find no difference.

Laurentiu,

if the public server role is normal, means i'm still lost. i have check the server role 'public' in my application database, everything looks just fine and normal, none of the schema is owned, but it is still accessible for my 'yy' login.

i've been workin on this for quite sometime and still not manage to find a solution. is there any other reason that can cause this Thank q





Re: Login without Connect Permission somehow managed to become db_owner!?

Laurentiu Cristofor

I'm out of ideas. Here are the two suggestions I have left:

(1) Try to reproduce the same issue on another SQL Server instance. You could install SQL Express and try to recreate the login and the database, as you did it on the original system - see if you can get the same behavior.

(2) You could try to backup all your SQL Server databases, then strip them of all data (whether it's sensitive or not), so you leave just the definitions of the login and database, and the permissions set for these. Verify that the issue still happens.

Once you get the problem isolated in a database with no sensitive data, you can fill a report (using link in sticky forum post) and attach the databases to the report (master and the app database). For (2), you should restore your databases after filling the report.

Thanks
Laurentiu






Re: Login without Connect Permission somehow managed to become db_owner!?

Raul Garcia - MS

Here is another idea. It may be possible that somehow this principal is a member of some role that is a member of db_owner. Can you please run the following query using the affected context:

SELECT * FROM sys.login_token ORDER BY type, usage, name

SELECT * FROM sys.user_token ORDER BY type, usage, name

go

Thanks,

-Raul Garcia

SDE/T

SQL Server Engine