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

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

Date: Tue, 20 Apr 2004 08:18:21 -0500

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

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)
  • 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)
  • Re: Knowledge Base Article - 815154 Configure SQL Server Security for .NET Applications
    ... db_ roles were inclusive in this context and I'll find the script very ... >> Do I as the admin have to check each and every object one at a time? ... > your application references nor what permissions ... > the user to the fixed db_datareader database role. ...
    (microsoft.public.sqlserver.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)