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