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

SEE: http://support.microsoft.com/defaul...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/> 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@.metromilwauke
e.com> wrote in message
news:OrW5lboJEHA.428@.TK2MSFTNGP11.phx.gbl...
> SEE: http://support.microsoft.com/defaul...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/
>|||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...
database[vbcol=seagreen]
> 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@.metromilwauke
e.com> wrote in
message
> news:OrW5lboJEHA.428@.TK2MSFTNGP11.phx.gbl...
database[vbcol=seagreen]
>|||> Are there any papers you could recommend I read that document
> what may be a base security configuration?
Check out Building Secure ASP.NET Applications
<http://msdn.microsoft.com/library/d...n-us/dnnetsec/h
tml/secnetlpMSDN.asp?frame=true>.
Hope this helps.
Dan Guzman
SQL Server MVP
"clintonG" < csgallagher@.REMOVETHISTEXT@.metromilwauke
e.com> wrote in message
news:OLt8MuvJEHA.3216@.tk2msftngp13.phx.gbl...
> 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...
> database
objects[vbcol=seagreen]
> are
> add
> procedure
> database
> all
> remote
> message
> database
>|||That article looks good. Thanks again Dan.
<%= Clinton Gallagher
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:enSrNK0JEHA.4072@.TK2MSFTNGP12.phx.gbl...
> Check out Building Secure ASP.NET Applications
>
<http://msdn.microsoft.com/library/d...n-us/dnnetsec/h
> tml/secnetlpMSDN.asp?frame=true>.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "clintonG" < csgallagher@.REMOVETHISTEXT@.metromilwauke
e.com> wrote in
message
> news:OLt8MuvJEHA.3216@.tk2msftngp13.phx.gbl...
id[vbcol=seagreen]
> objects
etc.)[vbcol=seagreen]
can[vbcol=seagreen]
for[vbcol=seagreen]
Development[vbcol=seagreen]
>