Re: Knowledge Base Article - 815154 Configure SQL Server Security for .NET Applications

From: clintonG (csgallagher_at_REMOVETHISTEXT@metromilwaukee.com)
Date: 04/20/04

  • Next message: Sajal Kumar: "TCPIP vs Named Pipes" 
    Date: Tue, 20 Apr 2004 12:18:59 -0500

    Yes, your comments helped Dan, thank you. I didn't know the
    db_ roles were inclusive in this context and I'll find the script very
    useful.

    I'm planning to include SQL Server as one of my MCAD electives
    but for now remain quite clueless about how to resolve security issues.

    For example, why my ASP.NET code can connect to the SQL Server
    when using Integrated Security or sa but not when using any other user id
    despite the presence of a SQL Server user I thought I had created
    correctly.

    In fact, I'm starting to understand the entire security model on my
    development machine is probably FUBAR as my ASP.NET
    applications all seem to run as the NT Authority rather than via the
    ASPNET worker process.

    Are there any papers you could recommend I read that document
    what may be a base security configuration?

    <%= Clinton Gallagher

    "Dan Guzman" <danguzman@nospam-earthlink.net> wrote in message
    news:OkePvntJEHA.2376@tk2msftngp13.phx.gbl...
    > > Do I as the admin have to check each and every object one at a time?
    > >
    > > Do I as the admin have to do all of these steps to each and every
    database
    > > I would want to configure for access by ASP.NET applications?
    >
    > Best practices dictate that you should grant permissions on only those
    > objects needed by your application. SQL Server doesn't know which objects
    > your application references nor what permissions (SELECT, UPDATE, etc.)
    are
    > required. Consequently, there is no real shortcut for the task.
    >
    > If your application reads from *all* tables and views directly, you can
    add
    > the user to the fixed db_datareader database role. If your application
    > writes to *all* tables and views, you can add the user to the fixed
    > db_datarwriter database role. You'll still need to assign stored
    procedure
    > execute permissions individually since there is currently no fixed
    database
    > role for this.
    >
    > Your admin might find it easier execute permission scripts using Query
    > Analyzer rather than the Enterprise Manager if you have a lot of
    > objects/databases. The script below will generate GRANT statements for
    all
    > database objects. You can tweak the script to fit your needs and/or
    remote
    > unneeded GRANTs from the generated script.
    >
    > SET NOCOUNT ON
    >
    > DECLARE @Permissions TABLE (Permission nvarchar(10))
    > INSERT INTO @Permissions VALUES('SELECT')
    > INSERT INTO @Permissions VALUES('INSERT')
    > INSERT INTO @Permissions VALUES('UPDATE')
    > INSERT INTO @Permissions VALUES('DELETE')
    > INSERT INTO @Permissions VALUES('EXECUTE')
    >
    > SELECT
    > N'GRANT ' +
    > [p].[Permission] +
    > N' ON ' +
    > QUOTENAME(USER_NAME([o].[uid])) +
    > N'.' +
    > QUOTENAME([o].[name]) +
    > N' TO MyUser'
    > FROM sysobjects o
    > CROSS JOIN @Permissions p
    > WHERE
    > OBJECTPROPERTY([o].[id], 'IsMSShipped') = 0 AND
    > ((OBJECTPROPERTY([o].[id], 'IsProcedure') = 1 AND
    > [p].[Permission] = N'EXECUTE') OR
    > (OBJECTPROPERTY([o].[id], 'IsUserTable') = 1 AND
    > [p].[Permission] <> N'EXECUTE') OR
    > (OBJECTPROPERTY([o].[id], 'IsView') = 1 AND
    > [p].[Permission] <> N'EXECUTE') OR
    > (OBJECTPROPERTY([o].[id], 'IsTableFunction') = 1 AND
    > [p].[Permission] <> N'EXECUTE') OR
    > (OBJECTPROPERTY([o].[id], 'IsInlineFunction') = 1 AND
    > [p].[Permission] = N'EXECUTE') OR
    > (OBJECTPROPERTY([o].[id], 'IsScalarFunction') = 1 AND
    > [p].[Permission] = N'EXECUTE'))
    > ORDER BY
    > [p].[Permission],
    > USER_NAME([o].[uid]),
    > [o].[name]
    >
    >
    > --
    > Hope this helps.
    >
    > Dan Guzman
    > SQL Server MVP
    >
    > "clintonG" <csgallagher@REMOVETHISTEXT@metromilwaukee.com> wrote in
    message
    > news:OrW5lboJEHA.428@TK2MSFTNGP11.phx.gbl...
    > > SEE: http://support.microsoft.com/default.aspx?scid=kb;en-us;815154
    > >
    > > I get through this article and come to a dead stop not understanding
    > > the best way to implement Step 14. Click (and apply) Permissions
    > > to the objects in the database.
    > >
    > > Do I as the admin have to check each and every object one at a time?
    > >
    > > Do I as the admin have to do all of these steps to each and every
    database
    > > I would want to configure for access by ASP.NET applications?
    > >
    > > --
    > > <%= Clinton Gallagher
    > > A/E/C Consulting, Web Design, e-Commerce Software Development
    > > Wauwatosa, Milwaukee County, Wisconsin USA
    > > NET csgallagher@REMOVETHISTEXTmetromilwaukee.com
    > > URL http://www.metromilwaukee.com/clintongallagher/
    > >
    > >
    >
    >

  • Next message: Sajal Kumar: "TCPIP vs Named Pipes"

    Relevant Pages

    • Re: User Level Security Malfunction
      ... menu, the Users group has no rights to open the database, and it also ... I first create a blank secured file that I ... Remove Admin from Admins group ... Remove all permissions to "new" objects from Admin ...
      (microsoft.public.access.security)
    • Q: low permission cannot convert from A97 to A2000/2003
      ... A97, but the database is sent out and used by contractors (user permissions) ... The admin user has full privileges. ... The problem we are having is converting the database from A97 to 2000+. ...
      (microsoft.public.access.security)
    • Sorting out security
      ... MS Access security. ... created a new workgroup, added a password for the Admin role, added groups, ... user IDs and passwords for users and allocated permissions on the ... remote logins to a secured database. ...
      (microsoft.public.access.security)
    • Security Wont work
      ... I have set up a database and created a workgoup file. ... the admins group so admin is only a member of the user ... All permissions for the user group are turned off. ... I see in Snelling's security document that there are two ...
      (microsoft.public.access.security)
    • Exclusive locks
      ... When I secured the database, I created two new users, ... "Full Permissions" and "Full Data Users." ... groups Full Permissions and Users (the users "Admin" and "Staff" are exactly ... Why isn't Access able to obtain the exclusive lock that it's apparently ...
      (microsoft.public.access.security)