Re: Logging in irrespective of database access

From: Dan Guzman (danguzman_at_nospam-earthlink.net)
Date: 07/16/04 

Date: Thu, 15 Jul 2004 19:22:34 -0500

Try connecting using Query Analyzer with one of the problem logins and run
the following in the context of your demodata and accounts databases:

    SELECT USER

This will return the security context used to access the database. 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"Laphan" <news@DoNotEmailMe.co.uk> wrote in message
news:40f6c5a0$1_3@127.0.0.1...
> Hi All
>
> Wonder if you could shed some light on a really weird problem.
>
> Basically we use a standard client side app that comms with 2 x SQL 2000
db.
>
> This SQL 2000 db sits on a Win2003 Server quite happily.
>
> The client apps sit on WinXP or Mac OS 9 quite happily.
>
> In SQL Enterprise Manager, I had originally created logins as follows:
>
> uid: demo   pwd: demo   only has access to demodata db
>
> uid: peter    pwd: peter   only has access to accounts db
> uid: kerry    pwd: kerry    only has access to accounts db
> uid: gary     pwd: gary     only has access to accounts db
>
> The reason for the above is so that nobody blindly logs into the demodata
db
> and enters account transactions all day.
>
> The above uses SQL server security and has worked fine for weeks until
this
> week.  Now for some reason I can log into either db with any of the above
> usernames and passwords.
>
> The security bit is working because if I leave out the username or
password
> or type completely different login details I can't get in, but for some
> reason the database access bit has gone up the spout !!!
>
> I've looked back at the Logins section in SQL Enterprise Manager and the
> Database Access is set correctly and I've checked that the ODBC data
sources
> don't have the 'sa' login details in that 'Prepared Statement...' bit, so
> I'm at a loss as to how or why this is happening.
>
> The only thing that I know we have done in SQL Server this week is that we
> had a backup from one of our clients and we restored this database into
our
> SQL Server installation.  The db works fine, even though there are
orphaned
> user entries under the Database/Users section in Enterprise Manager from
> their system, so could this be the culprit?  None of their logins have
> transposed to our system though.
>
> I've even created a new login and even though I'm only giving it access to
> the demodata db its still able to get into both SQL dbs.
>
> Do you know how this has happened and how I can correct it??
>
> Rgds
>
> Laphan
>
>
>
>

Relevant Pages

  • Re: Logging in irrespective of database access
    ... Try connecting using Query Analyzer with one of the problem logins and run ... the following in the context of your demodata and accounts databases: ... This will return the security context used to access the database. ... SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: How do determine who is the owner of an object?
    ... ths sys schema was introduced in SQL 2005 - aren't we were talking about SQL 2000 here? ... The TEST database will still to be owned by your Windows account. ... If I logon to my local machine that is running SQL Server 2000 as administrator, and then logon to SQL Server using Windows Authentication I am a member of the sysadmin server role even though I don't have a SQL Server login explicitly created for Administrator. ... logins do not own database objects directly. ...
    (microsoft.public.sqlserver.security)
  • Re: Server or Service Accounts complete lockdown?
    ... SQL Enterprise Manager? ... I wonder if I need to somehow create accounts in each SQL DB to allow ... Heres the strange bit - I have full access but dev team are prompted to ... used by the web application for SQL database access. ...
    (microsoft.public.security)
  • Re: Logins, Users, Roles, Schemas
    ... How do you delete these from a database? ... the logins from the database? ... Passwords Between SQL Servers ... Thus, I believe making Users, Roles, Schemas ...
    (microsoft.public.sqlserver.security)
  • Re: Changing security authentication type.
    ... i'm thinking that yes in time the sa account ... A new DBA Admin User login would only be useful for SQL Login purposes. ... if appropriate you can add users to the db_owner role in a database. ... Create one or more logins for the applications. ...
    (microsoft.public.sqlserver.security)