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

Kinda new to SQL, have a few questions

I have to update my database format to msSQL (any version) and the specific version I will be using I guess is unknown.

1. Security wise is there anything I should have installed? Can databases be accessed remotely through anything other than serverside scripts or software?
2. Backups will be important and so does msSQL allow you to do this easily? Is there a function in the program that will allow automatic backups? Or, I have autobackup software that basically zips files, would this work for msSQL?

Will probably think of some more questions :P

Thanks a lotIn answer to your second question (2) : Yes MSSQL allows scheduled backups from within the management studio interface. You can set up a scheduled job and get it to run on a daily basis. You need to set up a backup device (file area). Usually advisable to have a seperate drive for this (although a partition MIGHT suffice, except in disk failure scenarios).|||Thanks, when you say a separate drive is this due to performance or loss of data due to disk failure etc?
Is this a feature of all versions?

Thanks again|||ummmmm....do you have sql server client tools installed?

Start there, then get back to us

If you don't have access to a sql server or the tools, this will be very difficult to help you|||This was just general research before I go out and buy anything expensive|||Depending upon your requirements you could start with SQL Server Express, which is very cheap (free, I think).|||yes, it's free. has similar limitations as MSDE, like no more than 4gb database size, etc.|||Ok then thanks, did not know there was a free version.
What else should I add to my checklist of things to install?

sql server client tools?|||there is a free client to go along with the free server. client is SQL Server Management Studio Express, server is SQL Server Express.

you can get both here: http://msdn.microsoft.com/vstudio/express/sql/download/

Kill SPID Minimum SQL Server Privileges

What are the minimum SQL Server 2000 security privileges that a user needs
to use
the KILL command within SQL Server?
Thanks,This is a multi-part message in MIME format.
--020305000807010203020401
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
KILL permissions default to the members of the *sysadmin* and
*processadmin* fixed database roles, and are not transferable.
See BOL:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_kf-kz_1zos.asp
--
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Joe K. wrote:
>What are the minimum SQL Server 2000 security privileges that a user needs
>to use
>the KILL command within SQL Server?
>Thanks,
>
--020305000807010203020401
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=UTF-8" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<tt>KILL permissions default to the members of the <b>sysadmin</b> and
<b>processadmin</b> fixed database roles, and are not transferable.<br>
<br>
See BOL:<br>
<a class="moz-txt-link-freetext" href="http://links.10026.com/?link=http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_kf-kz_1zos.asp</a><br>">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_kf-kz_1zos.asp">http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_kf-kz_1zos.asp</a><br>
</tt>
<div class="moz-signature">
<title></title>
<meta http-equiv="Content-Type" content="text/html; ">
<p><span lang="en-au"><font face="Tahoma" size="2">--<br>
</font> </span><b><span lang="en-au"><font face="Tahoma" size="2">mike
hodgson</font></span></b><span lang="en-au"> <font face="Tahoma"
size="2">|</font><i><font face="Tahoma"> </font><font face="Tahoma"
size="2"> database administrator</font></i><font face="Tahoma" size="2">
| mallesons</font><font face="Tahoma"> </font><font face="Tahoma"
size="2">stephen</font><font face="Tahoma"> </font><font face="Tahoma"
size="2"> jaques</font><font face="Tahoma"><br>
</font><b><font face="Tahoma" size="2">T</font></b><font face="Tahoma"
size="2"> +61 (2) 9296 3668 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2"> F</font></b><font face="Tahoma" size="2"> +61
(2) 9296 3885 |</font><b><font face="Tahoma"> </font><font
face="Tahoma" size="2">M</font></b><font face="Tahoma" size="2"> +61
(408) 675 907</font><br>
<b><font face="Tahoma" size="2">E</font></b><font face="Tahoma" size="2">
<a href="http://links.10026.com/?link=mailto:mike.hodgson@.mallesons.nospam.com">
mailto:mike.hodgson@.mallesons.nospam.com</a> |</font><b><font
face="Tahoma"> </font><font face="Tahoma" size="2">W</font></b><font
face="Tahoma" size="2"> <a href="http://links.10026.com/?link=/">http://www.mallesons.com">
http://www.mallesons.com</a></font></span> </p>
</div>
<br>
<br>
Joe K. wrote:
<blockquote cite="midCA1BF761-8330-4FC4-831B-09BBE934FADB@.microsoft.com"
type="cite">
<pre wrap="">What are the minimum SQL Server 2000 security privileges that a user needs
to use
the KILL command within SQL Server?
Thanks,
</pre>
</blockquote>
</body>
</html>
--020305000807010203020401--sql

Kill SPID Minimum SQL Server Privileges

What are the minimum SQL Server 2000 security privileges that a user needs
to use
the KILL command within SQL Server?
Thanks,
KILL permissions default to the members of the *sysadmin* and
*processadmin* fixed database roles, and are not transferable.
See BOL:
http://msdn.microsoft.com/library/de...kf-kz_1zos.asp
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Joe K. wrote:

>What are the minimum SQL Server 2000 security privileges that a user needs
>to use
>the KILL command within SQL Server?
>Thanks,
>

Kill SPID Minimum SQL Server Privileges

What are the minimum SQL Server 2000 security privileges that a user needs
to use
the KILL command within SQL Server?
Thanks,KILL permissions default to the members of the *sysadmin* and
*processadmin* fixed database roles, and are not transferable.
See BOL:
http://msdn.microsoft.com/library/d...br />
1zos.asp
*mike hodgson* |/ database administrator/ | mallesons stephen jaques
*T* +61 (2) 9296 3668 |* F* +61 (2) 9296 3885 |* M* +61 (408) 675 907
*E* mailto:mike.hodgson@.mallesons.nospam.com |* W* http://www.mallesons.com
Joe K. wrote:

>What are the minimum SQL Server 2000 security privileges that a user needs
>to use
>the KILL command within SQL Server?
>Thanks,
>

Kill

I need to let a third party security app run a script as a
system admin to drop and recreate a database. Before this
will run, of course I need to make sure all connections to
that database are dropped.
Is there a command that will kill all connections to a
database?Sometimes you just have to trace other programs that can do this. I traced
what happens when you disconnect a database and someone is using it.
select spid from master..sysprocesses where dbid=db_id('<database name>')
Then that spid result is fed to a kill statement.
Should warn you that this is a tricky thing that you are doing. Certain
kinds of connections, such as those with SQL Query Analyzer and Enterprise
Manager, do not drop very easily. Sometimes connections keep going. A
drastic step might be to use a net stop/net start to restart MSSQLserver.
That will certainly free up all the connections, though the database might
go into recovery.
But no, if the Clear connection button on the Detach Database function in
SQL EM doesn't call a command, I doubt you are going to find one.
--
*******************************************************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@.NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
*******************************************************************
"gotit" <anonymous@.discussions.microsoft.com> wrote in message
news:00a701c3d3b6$6be8e6c0$a401280a@.phx.gbl...
> I need to let a third party security app run a script as a
> system admin to drop and recreate a database. Before this
> will run, of course I need to make sure all connections to
> that database are dropped.
> Is there a command that will kill all connections to a
> database?|||Add these lines to the top of the script.
ALTER DATABASE 'MyDBName' SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE 'MyDBName' SET ONLINE
GO
--
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
"gotit" <anonymous@.discussions.microsoft.com> wrote in message
news:00a701c3d3b6$6be8e6c0$a401280a@.phx.gbl...
> I need to let a third party security app run a script as a
> system admin to drop and recreate a database. Before this
> will run, of course I need to make sure all connections to
> that database are dropped.
> Is there a command that will kill all connections to a
> database?|||I know that I've seen a stored procedure on the net that will kill all
user connections. Try doing a search in google for something like "sp
kill all users" without the quotes.
Aaron
Andy Svendsen wrote:
> Sometimes you just have to trace other programs that can do this. I traced
> what happens when you disconnect a database and someone is using it.
> select spid from master..sysprocesses where dbid=db_id('<database name>')
> Then that spid result is fed to a kill statement.
> Should warn you that this is a tricky thing that you are doing. Certain
> kinds of connections, such as those with SQL Query Analyzer and Enterprise
> Manager, do not drop very easily. Sometimes connections keep going. A
> drastic step might be to use a net stop/net start to restart MSSQLserver.
> That will certainly free up all the connections, though the database might
> go into recovery.
> But no, if the Clear connection button on the Detach Database function in
> SQL EM doesn't call a command, I doubt you are going to find one.
>sql