Re: Logging in irrespective of database access

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

Date: Fri, 16 Jul 2004 08:33:09 -0500

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

I expected either 'dbo' or 'guest'.

Sysadmin role members have full permissions, bar none. A sysadmin role
member login shouldn't be used for routine application access.

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

Even though you can control access from within your app, there is nothing
that will prevent users from using another tool to access the database
directly. It is best to grant only those permissions needed. You might
consider application roles if this is a concern.

You can generate and/or execute a SQL script that grants the permissions
needed following your schema upgrades. Example below.

--Grant all permissions to specified role
SET NOCOUNT ON

DECLARE @GrantStatement nvarchar(500)
DECLARE @LastError int

DECLARE GrantStatements CURSOR LOCAL FAST_FORWARD FOR
SELECT
    N'GRANT ALL ON ' +
    QUOTENAME(USER_NAME([ob].[uid])) + '.' + QUOTENAME([ob].[name]) +
    ' TO MyRole'
FROM
    sysobjects ob
WHERE
    OBJECTPROPERTY([ob].[id], 'IsMSShipped') = 0 AND
    (OBJECTPROPERTY([ob].[id], 'IsProcedure') = 1 OR
    OBJECTPROPERTY([ob].[id], 'IsUserTable') = 1 OR
    OBJECTPROPERTY([ob].[id], 'IsView') = 1 OR
    OBJECTPROPERTY([ob].[id], 'IsInlineFunction') = 1 OR
    OBJECTPROPERTY([ob].[id], 'IsScalarFunction') = 1 OR
    OBJECTPROPERTY([ob].[id], 'IsTableFunction') = 1)
OPEN GrantStatements
WHILE 1 = 1
BEGIN
    FETCH NEXT FROM GrantStatements INTO @GrantStatement
    IF @@FETCH_STATUS = -1 BREAK
    RAISERROR (@GrantStatement, 0, 1) WITH NOWAIT
    EXECUTE sp_ExecuteSQL @GrantStatement
END
CLOSE GrantStatements
DEALLOCATE GrantStatements 

-- 
Hope this helps.
Dan Guzman
SQL Server MVP
"Astra" <info@NoEmail.com> wrote in message
news:ucLKLlyaEHA.4036@TK2MSFTNGP11.phx.gbl...
> 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: Logging in irrespective of database access
    ... Sysadmin role members have full permissions, ... SQL Server MVP ... > 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. ... > What's the point in having the Database Access section if the System Admin> under Server Roles allows you to get into anything?. ...
    (microsoft.public.sqlserver.server)
  • Re: Logging in irrespective of database access
    ... Sysadmin role members have full permissions, ... SQL Server MVP ... > 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. ... > What's the point in having the Database Access section if the System Admin> under Server Roles allows you to get into anything?. ...
    (microsoft.public.sqlserver.programming)
  • Re: Logging in irrespective of database access
    ... Sysadmin role members have full permissions, ... SQL Server MVP ... > 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. ... > What's the point in having the Database Access section if the System Admin> under Server Roles allows you to get into anything?. ...
    (microsoft.public.sqlserver.programming)
  • Re: Logging in irrespective of database access
    ... My problem is that in the Login section of Enterprise Manger I have to ... like there used to be in SQL 6.5. ... What's the point in having the Database Access section if the System Admin ... SQL Server MVP ...
    (microsoft.public.sqlserver.programming)
  • Re: Logging in irrespective of database access
    ... My problem is that in the Login section of Enterprise Manger I have to ... like there used to be in SQL 6.5. ... What's the point in having the Database Access section if the System Admin ... SQL Server MVP ...
    (microsoft.public.sqlserver.server)