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

Keeping text format after stored in the data base.

I want the users of my site to be able to write a couple of paragraphs, save it to SQL Server and then have it read back and look the same. I don't want the users to be able to add any html to the text they submit. I just want them to be able to seperate or indent their paragraphs.

At one point, I had a textbox that saved text to the server and read it back the same way it was originally inputed. However, I can't figure our what I had done to make it work.

Any ideas?
ThanksIt's usually an issue between cariage return and line feed characters and <br> tags. Depending on where/how you're displaying the text, you probably have to replace one with the other.|||For those that are interested.
I figured out that I can look at the ASCII value of each Char in the string then use a switch statement
public string FormatText(string textBlock)
{
//return textBlock;4444
string formattedText = "";
int charCount = 0;
foreach(char x in textBlock)
{
charCount++;
switch((int)x)
{
case 10:
charCount = 0;
formattedText += "<br>";
break;
case 32:
if(charCount > 60)
{
formattedText += "<br>";
charCount = 0;
}
else
formattedText += " ";
break;
default:
formattedText += x;
break;
}
}
return formattedText;
}

Keep track of data base versions

Hi

We have several data bases and we update them every once in a while.

We want to mark each current state witha version in order to be able to track the changes made between 2 versions.

Any recommended tool?

Thanks

Avi harush

Check in Books Online about using Extended Properties.

Topics:

sp_addextendedproperty

sp_updateextendedproperty

fn_listextendedproperty

Code Snippet

EXECUTE sp_updateextendedproperty 'Version', 'v 1.0'
SELECT * FROM ::fn_listextendedproperty
( 'Version', default, default, default, default, default, default )
EXECUTE sp_updateextendedproperty 'Version', 'v 1.1'
SELECT * FROM ::fn_listextendedproperty
( 'Version', default, default, default, default, default, default )

|||

Thanks

Isn't there a product for doing this?

Avi

|||

I don't know of a third party tool to manage this.

But it would be very simple to create a couple of stored procedures (or script files) that can be used by your Upgrade process to check the version and either inform or take appropriate action.

kbBug KB810885 - Patch available

We are experiencing kbBug KB810885 and would like to download the patch. But according to the info in the knowledge base we have to contact support to get the patch. Is there some other way to get the patch without have to pay for it?
KB810885 - BUG: High-end Disk Subsystems May Experience Error 17883The knowledge base article states that you must contact MS support to get
the patch. This means, and sorry for repeating this, you must contact MS
support to get the patch. If it's a bug, you don't have to pay for the
support incident (as long as you don't bring up a bunch of other crap that
has nothing to do with the symptom this patch should fix).
"Molonede" <anonymous@.discussions.microsoft.com> wrote in message
news:6978A7C6-501A-4986-BE44-EC091C0683E3@.microsoft.com...
> We are experiencing kbBug KB810885 and would like to download the patch.
But according to the info in the knowledge base we have to contact support
to get the patch. Is there some other way to get the patch without have to
pay for it?
> KB810885 - BUG: High-end Disk Subsystems May Experience Error 17883|||(BTW, I don't see anywhere in that article that suggests a patch is
available.)
"Molonede" <anonymous@.discussions.microsoft.com> wrote in message
news:6978A7C6-501A-4986-BE44-EC091C0683E3@.microsoft.com...
> We are experiencing kbBug KB810885 and would like to download the patch.
But according to the info in the knowledge base we have to contact support
to get the patch. Is there some other way to get the patch without have to
pay for it?
> KB810885 - BUG: High-end Disk Subsystems May Experience Error 17883