Re: Logging in irrespective of database access
From: Laphan (news_at_DoNotEmailMe.co.uk)
Date: 07/17/04
- Next message: Sam: "Shrinking the log file in sql2000"
- Previous message: jn316sc: "Developer Edition & Windows Server 2003 Small Business Server"
- In reply to: Dan Guzman: "Re: Logging in irrespective of database access"
- Messages sorted by: [ date ] [ thread ]
Date: Sat, 17 Jul 2004 23:02:15 +0100
Thanks Dan
Dan Guzman <danguzman@nospam-earthlink.net> wrote in message
news:ORiYvlzaEHA.3716@TK2MSFTNGP11.phx.gbl...
> 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 > > > > > > > > > > >
- Next message: Sam: "Shrinking the log file in sql2000"
- Previous message: jn316sc: "Developer Edition & Windows Server 2003 Small Business Server"
- In reply to: Dan Guzman: "Re: Logging in irrespective of database access"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
|
