Logging in irrespective of the database access settings

From: Astra (info_at_NoEmail.com)
Date: 07/16/04 

Date: Fri, 16 Jul 2004 12:49:13 +0100

Hi Dan

Thanks for coming back to me.

I think you expected it to say dbo and it does for each user.

My problem is that in the Login section of Enterprise Manger I have to
specify that each login has the System Admin ticked under Server Roles
because in SQL 2000 there doesn't seem to a Grant All permissions option
like there used to be in SQL 6.5.

As our client-side app controls the permissions, we need each user to have
grant all access to all tables and sprocs and ticking these manually is a
no-no.

Couple that with the fact that when a new version of the client-side app
comes out we have to re-install sprocs to accommodate the new features this
means we have to re-do the permissions each time. Ticking the System Admin
option elevates this problem and I'm sure everything was working fine before
with this ticked for each user.

What's the point in having the Database Access section if the System Admin
under Server Roles allows you to get into anything?.

Regards

Robbie

"Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message
news:Oyhu9rsaEHA.2408@tk2msftngp13.phx.gbl...
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: How do i move an SQLDatabase to another location?
    ... I went to my clients and installed SQL Server ... Express and copied my database to the same location it was in while i was ... knowing that i can bring a database with me and install ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: System Administrator Implied Permissions
    ... > sa login, it assigns it the System Administrator fixed ... > Now, given this, why does SQL Server ... in each database is always a member of the public and db_owner roles. ... Other sysadmin role members have the exact same ...
    (microsoft.public.sqlserver.security)
  • Re: No db access after publishing web site
    ... GRANT UPDATE TO ... If I detach and attach this database on a different PC (according that PC ... Cannot open database "pago" requested by the login. ... Are you detaching/attaching the SQL Server Express database correctly ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: SQLDriverConnect error
    ... > The error says that for the user that is logging into SQL Server, ... > For login 001, you need to give the user another default database. ...
    (microsoft.public.sqlserver.connect)
  • Re: New to Windows CE Development - Have some questions
    ... validating against the main database, or querying the database during data ... iterate through the SQL CE table and create new entries in the ... SQL Server database that correspond. ... >>> not connected to the Enterprise. ...
    (microsoft.public.windowsce.app.development)