Removing User Right Assignments form SQL-Server Account

Hello everybody,
I'm currently securing a w2k box according to some recommendations
made by a company that audited our server.
Now among their recommendations is to remove the following rights from
the sql-server account. (The SQL Server runs under its own 'user
account' and in mixed mode)
According to them I should remove these permissions:
- Act as part of the Operating System
- Replace a process level token
- Increase quotas
- Log on as batch job
- Log on as service
I've browsed the web and found some documents, however none could
really answer which SQL-Features depend on these services.
Maybe someone in this forum has some experience or helpful urls.
Thanks very much.
the document:
SQL Server 2000 C2 Administrator's and User's Security Guide
at http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/maintain/security/sqlc2.asp
somehow mentions that these four services are necessary, but not why.
* Act as part of the operating system.
* Increase quotas.
* Replace a process-level token.
* Log on as a service.Hallo Beat(e) M=FCller,
at first you must NOT REMOVE the right
- Log on as service (The sql-Server does not start anymore!)
second the others you may try but I do not suggest it. Please look at
http://www.microsoft.com/sql/techinfo/administration/2000/s
ecurity/securingsqlserver.asp
there are useful tipps to securing the sql-Server
- Log on as a batch job (removable depends on your Envoirement - !CHECK! does your sqlserver service use a start batch?)
- the others will be used for internal tuning and interaction - I you REALLY must to remove them try it on your on risk!
CU Ralf

Removing individual results from a paged set of results.

Hi,

I have a web form that lets users search for people in my database they wish to contact. The database returns a paged set of results using a CTE, Top X, and Row_number().

I would like to give my users to option of removing individual people from this list but cannot find a way to do this.

I have tried creating a session variable with a comma delimited list of ID's that I pass to my sproc and use in a NOT IN() statement. But I keep getting a "Input string was not in a correct format." Error Message.

Is there any way to do this? I am still new to stored procedures so any advice would be helpful.

Thanks

The only way I know to do it is to use dynamic sql.

|||

Can you post your SQL stored procedure? Its a lot easier to give suggestions if we can see what we're working with. There are several ways to do this, but I'd like to see what I'm working with before I make a suggestion.

Stu

|||

This is the SPROC I am currently working with without any code to remove individual rows.

CREATE PROCEDURE [dbo].[zk_update_request_england]
(
@.property_type tinyint,
@.market_status tinyint,
@.price int,
@.bedrooms tinyint,
@.search_location varchar(30),
@.PageSize int,
@.PageIndex int,
@.TRV int,
@.topx int,
@.TotalRequests int OUTPUT
)

AS

SET NOCOUNT ON

BEGIN

-- Set Paging limits
Declare @.firstRow Int;
Declare @.lastRow Int;
Set @.firstRow = (@.PageIndex * @.PageSize) + 1;
Set @.lastRow = @.firstRow + @.PageSize - 1;

-- Load count into @.TotalRequests
Set @.TotalRequests = (SELECT Count(*)
FROM [dbo].[zk_request_england]
WHERE property_type = @.property_type
AND market_status = @.market_status
AND bedrooms <= @.bedrooms
AND search_location = @.search_location
AND min_price <= @.price
AND max_price >= @.price)

-- If @.TotalRequests is less than @.topx change @.topx to @.TotalRequests
IF @.TotalRequests <= @.topx
SET @.topx = @.TotalRequests
ELSE
SET @.TotalRequests = @.topx
;

WITH SearchResults AS
(
SELECT TOP(@.topx) ROW_NUMBER() OVER (ORDER BY max_price DESC) AS RowNumber,
id,
user_name,
bedrooms,
min_price,
max_price,
property_description,
searched
FROM [dbo].[zk_request_england]
WHERE property_type = @.property_type
AND market_status = @.market_status
AND bedrooms <= @.bedrooms
AND search_location = @.search_location
AND min_price <= @.price
AND max_price >= @.price
)

SELECT id,
user_name,
bedrooms,
min_price,
max_price,
property_description
FROM SearchResults
WHERE RowNumber BETWEEN @.firstRow and @.lastRow

END

SET NOCOUNT OFF

removing all data from all users table

Hi All,
I have over 200 tables in a database and I like to know
if there is a Transact-SQL Reference that would remove
all rows form ALL tables in the database. I am not sure
if this is possible because some tables are referenced by
foreign key constraints.
TRUNCATE TABLE name only does one table at a time and
it's too time consuming to use it to remove all rows from
all tables.
Thank you,
MitraYou'll still have issues with foreign keys but you can do this:
EXEC sp_msForEachTable 'TRUNCATE TABLE ?'
Or you can generate a script this way:
SELECT 'TRUNCATE TABLE '+TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
When you run this, it will generate a series of statements in the lower
pane. You can copy this to the top pane (or new query window) and execute
it. You might consider adding a GO to the SELECT so that any errors on
individual truncate commands don't abort the whole batch; the list of errors
at the bottom will be your guide to tables you need to disable constraints
or otherwise handle individually.
You could also consider wiping out the database and re-creating it.
Hopefully you have scripts for all the tables/procedures etc. in SourceSafe,
and automating this should be trivial (especially if you're going to do this
periodically).
http://www.aspfaq.com/
(Reverse address to reply.)
"mitra fatholahi" <mitra928@.hotmail.com> wrote in message
news:17b6601c449e2$b99060e0$a601280a@.phx
.gbl...
> Hi All,
> I have over 200 tables in a database and I like to know
> if there is a Transact-SQL Reference that would remove
> all rows form ALL tables in the database. I am not sure
> if this is possible because some tables are referenced by
> foreign key constraints.
> TRUNCATE TABLE name only does one table at a time and
> it's too time consuming to use it to remove all rows from
> all tables.
> Thank you,
> Mitra|||Mitra,
If possible, cant you script out the entire database including the
indexes,triggers,relationships etc ,drop the database and then recreate the
database ?Since, as you mention the foreign key relationships exist, you
cannot do TRUNCATE TABLE unless you drop all those FKs and recreate them
later.Other option would be to prepare a DELETE FROM script on all tables,
making sure that the child tables appear first.I would adopt this only if I
cant drop the database.If you are going with DELETE, then you can do
something like:
SELECT 'DELETE FROM '+TABLE_SCHEMA+'.'+QUOTENAME(TABLE_NAME)+CHAR(13)+'GO'
FROM information_schema.tables
The above query would generate a script.Make necessary modifications and
execute the resultant script.If you are going with TRUNCATE you can adopt
the above script generation method.
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"mitra fatholahi" <mitra928@.hotmail.com> wrote in message
news:17b6601c449e2$b99060e0$a601280a@.phx
.gbl...
> Hi All,
> I have over 200 tables in a database and I like to know
> if there is a Transact-SQL Reference that would remove
> all rows form ALL tables in the database. I am not sure
> if this is possible because some tables are referenced by
> foreign key constraints.
> TRUNCATE TABLE name only does one table at a time and
> it's too time consuming to use it to remove all rows from
> all tables.
> Thank you,
> Mitra

removing all data from all users table

Hi All,
I have over 200 tables in a database and I like to know
if there is a Transact-SQL Reference that would remove
all rows form ALL tables in the database. I am not sure
if this is possible because some tables are referenced by
foreign key constraints.
TRUNCATE TABLE name only does one table at a time and
it's too time consuming to use it to remove all rows from
all tables.
Thank you,
MitraYou'll still have issues with foreign keys but you can do this:
EXEC sp_msForEachTable 'TRUNCATE TABLE ?'
Or you can generate a script this way:
SELECT 'TRUNCATE TABLE '+TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
When you run this, it will generate a series of statements in the lower
pane. You can copy this to the top pane (or new query window) and execute
it. You might consider adding a GO to the SELECT so that any errors on
individual truncate commands don't abort the whole batch; the list of errors
at the bottom will be your guide to tables you need to disable constraints
or otherwise handle individually.
You could also consider wiping out the database and re-creating it.
Hopefully you have scripts for all the tables/procedures etc. in SourceSafe,
and automating this should be trivial (especially if you're going to do this
periodically).
--
http://www.aspfaq.com/
(Reverse address to reply.)
"mitra fatholahi" <mitra928@.hotmail.com> wrote in message
news:17b6601c449e2$b99060e0$a601280a@.phx.gbl...
> Hi All,
> I have over 200 tables in a database and I like to know
> if there is a Transact-SQL Reference that would remove
> all rows form ALL tables in the database. I am not sure
> if this is possible because some tables are referenced by
> foreign key constraints.
> TRUNCATE TABLE name only does one table at a time and
> it's too time consuming to use it to remove all rows from
> all tables.
> Thank you,
> Mitra|||Mitra,
If possible, cant you script out the entire database including the
indexes,triggers,relationships etc ,drop the database and then recreate the
database ?Since, as you mention the foreign key relationships exist, you
cannot do TRUNCATE TABLE unless you drop all those FKs and recreate them
later.Other option would be to prepare a DELETE FROM script on all tables,
making sure that the child tables appear first.I would adopt this only if I
cant drop the database.If you are going with DELETE, then you can do
something like:
SELECT 'DELETE FROM '+TABLE_SCHEMA+'.'+QUOTENAME(TABLE_NAME)+CHAR(13)+'GO'
FROM information_schema.tables
The above query would generate a script.Make necessary modifications and
execute the resultant script.If you are going with TRUNCATE you can adopt
the above script generation method.
--
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"mitra fatholahi" <mitra928@.hotmail.com> wrote in message
news:17b6601c449e2$b99060e0$a601280a@.phx.gbl...
> Hi All,
> I have over 200 tables in a database and I like to know
> if there is a Transact-SQL Reference that would remove
> all rows form ALL tables in the database. I am not sure
> if this is possible because some tables are referenced by
> foreign key constraints.
> TRUNCATE TABLE name only does one table at a time and
> it's too time consuming to use it to remove all rows from
> all tables.
> Thank you,
> Mitra

removing all data from all users table

Hi All,
I have over 200 tables in a database and I like to know
if there is a Transact-SQL Reference that would remove
all rows form ALL tables in the database. I am not sure
if this is possible because some tables are referenced by
foreign key constraints.
TRUNCATE TABLE name only does one table at a time and
it's too time consuming to use it to remove all rows from
all tables.
Thank you,
Mitra
You'll still have issues with foreign keys but you can do this:
EXEC sp_msForEachTable 'TRUNCATE TABLE ?'
Or you can generate a script this way:
SELECT 'TRUNCATE TABLE '+TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
When you run this, it will generate a series of statements in the lower
pane. You can copy this to the top pane (or new query window) and execute
it. You might consider adding a GO to the SELECT so that any errors on
individual truncate commands don't abort the whole batch; the list of errors
at the bottom will be your guide to tables you need to disable constraints
or otherwise handle individually.
You could also consider wiping out the database and re-creating it.
Hopefully you have scripts for all the tables/procedures etc. in SourceSafe,
and automating this should be trivial (especially if you're going to do this
periodically).
http://www.aspfaq.com/
(Reverse address to reply.)
"mitra fatholahi" <mitra928@.hotmail.com> wrote in message
news:17b6601c449e2$b99060e0$a601280a@.phx.gbl...
> Hi All,
> I have over 200 tables in a database and I like to know
> if there is a Transact-SQL Reference that would remove
> all rows form ALL tables in the database. I am not sure
> if this is possible because some tables are referenced by
> foreign key constraints.
> TRUNCATE TABLE name only does one table at a time and
> it's too time consuming to use it to remove all rows from
> all tables.
> Thank you,
> Mitra
|||Mitra,
If possible, cant you script out the entire database including the
indexes,triggers,relationships etc ,drop the database and then recreate the
database ?Since, as you mention the foreign key relationships exist, you
cannot do TRUNCATE TABLE unless you drop all those FKs and recreate them
later.Other option would be to prepare a DELETE FROM script on all tables,
making sure that the child tables appear first.I would adopt this only if I
cant drop the database.If you are going with DELETE, then you can do
something like:
SELECT 'DELETE FROM '+TABLE_SCHEMA+'.'+QUOTENAME(TABLE_NAME)+CHAR(13)+ 'GO'
FROM information_schema.tables
The above query would generate a script.Make necessary modifications and
execute the resultant script.If you are going with TRUNCATE you can adopt
the above script generation method.
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"mitra fatholahi" <mitra928@.hotmail.com> wrote in message
news:17b6601c449e2$b99060e0$a601280a@.phx.gbl...
> Hi All,
> I have over 200 tables in a database and I like to know
> if there is a Transact-SQL Reference that would remove
> all rows form ALL tables in the database. I am not sure
> if this is possible because some tables are referenced by
> foreign key constraints.
> TRUNCATE TABLE name only does one table at a time and
> it's too time consuming to use it to remove all rows from
> all tables.
> Thank you,
> Mitra