How to deny information schema views...

Hello, all. I created a login and granted the user access to my sql 2005
database. well when the user creates his odbc dsn to access the database, I
discovered he can also see the INFORMATION_SCHEMA views. What gives? How
can I deny him access to these objects. He should have access to the db I
granted him.
Help!!!!!!!!
RozThe Information Schema views in SQL Server 2005 should only return for the
user information about the objects the user actually has access to. While
this was a prominent information disclosure issue in SQL Server 2000, it's
not as wide open in SQL Server 2005. They are provided for SQL-92 compliance
so that users can query the metadata/schema of the database without having
to query the system tables. Is there a reason you want to block access to
them?
K. Brian Kelley, brian underscore kelley at sqlpass dot org
http://www.truthsolutions.com/

> Hello, all. I created a login and granted the user access to my sql
> 2005 database. well when the user creates his odbc dsn to access the
> database, I discovered he can also see the INFORMATION_SCHEMA views.
> What gives? How can I deny him access to these objects. He should
> have access to the db I granted him.
> Help!!!!!!!!
> Roz|||Hello Roz,
You can't hide the fact the views exist as far as I can tell, but if you
look at what he see, it won't be much if anything. Basically he has to be
able to the see the metadata's metadata, but he shouldn't be able to see
the metadata itself unless you start granting him rights to do so (e.g.,
VIEW DEFINITION).
Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Thanks for reply. I want to block access because as my users create their
ODBC DSNs, they can open these tables and **change** data. I've tried it an
d
it works. Very scary.
Roz
"K. Brian Kelley" wrote:

> The Information Schema views in SQL Server 2005 should only return for the
> user information about the objects the user actually has access to. While
> this was a prominent information disclosure issue in SQL Server 2000, it's
> not as wide open in SQL Server 2005. They are provided for SQL-92 complian
ce
> so that users can query the metadata/schema of the database without having
> to query the system tables. Is there a reason you want to block access to
> them?
>
> K. Brian Kelley, brian underscore kelley at sqlpass dot org
> http://www.truthsolutions.com/
>
>
>|||Kent,
Simply having the "public" role, gets him access to these tables. He (I)
was even able to open these tables say in Access thru ODBC, and potentially
change the data. Scary.
Roz
"Kent Tegels" wrote:

> Hello Roz,
> You can't hide the fact the views exist as far as I can tell, but if you
> look at what he see, it won't be much if anything. Basically he has to be
> able to the see the metadata's metadata, but he shouldn't be able to see
> the metadata itself unless you start granting him rights to do so (e.g.,
> VIEW DEFINITION).
> Thanks!
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>|||I am wondering if you are seeing something else.
Could you please give us the steps you used to open
information schema views and change the underlying data on
SQL Server 2005? Which views, data in what columns?
As far as I know, what you are saying is not possible.
If it is actually other tables you are referring too, I
think you have a permissions issue with how you have
security set up. I think that's likely the issue anyway.
-Sue
On Tue, 20 Mar 2007 16:51:05 -0700, Roz
<Roz@.discussions.microsoft.com> wrote:
[vbcol=seagreen]
>Thanks for reply. I want to block access because as my users create their
>ODBC DSNs, they can open these tables and **change** data. I've tried it a
nd
>it works. Very scary.
>Roz
>"K. Brian Kelley" wrote:
>

HOW TO DELETE OLD BACK UP FILES AND TRANSACTION LOGS IN SQL 2005

SQL 2005 DOES NOT HAVE THE SAME FEATURE THAT WAS IN 2000 TO DELETE OLDER FILES OLDER THAN CERTAIN DAYS. I DO HAVE A JOB THAT CREATES A FULL DAILY BACKUPS BUT SINCE I DON'T HAVE ENOUGH SPACE, I WOULD LIKE TO FIND A WAY TO DELETE ALL OLD BACKUP FILES OLDER THAN X AMOUNT OF DAYS AND ALSO DELETE TRANSACTION FILES OLDER THAN X AMOUNT OF DAYS. IS THERE A TOOL TO DO THIS. CAN ANYONE HELP PLEASE.

EMADKB

The Maintenance Cleanup task removes files related to maintenance plans, including database backup files and reports created by maintenance plans.|||

Unfortunately - it does not seem to control deletion of the backup files!

It cleans out information from Datebase logs - but that is not really the problem.

At least from what we see - it does not delte the older files.

We have a cleanup running but older files do not go away.

Bob

|||

It's happening to me too. In our maintanance plans we have clean up tasks set to remove old files but no files are ever deleted. Even running EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'.bak',N'10/24/2006 13:12:32' manually still did not remove any files.

Anyone find a cure for this?

thanks!

|||The maintenance cleanup task will work but you need to specify the folder the files are in and the appropriate extension. SP1 does have an option for "include 1st level folders" in this case it will search the folder you specify and one level down. Also I seem to remember that for the backup files or the logs I needed the / at the end of the folder specification and one of them I didn't. We no longer use maintenance plans for backups and I don't remember which it was. Good Luck.|||There's definately a bug in the Maintanance Plan Clean up task, it will not delete any files. Until they fix it we'll have to do it via a SQL command.|||

There are multiple Microsoft Connect items around Maintenance Plans that we've addressed in SQL Server 2005 Service Pack 2. I am not sure why the Maintenance Plan clean up task is not working for you. In our labs, it is definitely working with pre-release service pack 2 bits.

Add Cleanup Task to Wizard
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126800

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||

We've installed SP1 and SQL 2k5 does not delete the old .bak nor .trn backup files. SP1 gave us the checkbox for sub-folders, but it still won't delete the old backups.

Unfortunately my 60+ production databases aren't setup in your Lab and I don't have pre-release service pack 2 bits, so I need to know what MS is going to do to help people in the real world, like me, delete the old backups?

|||We are also experiencing this problem with our production SQL 2005 SP1 databases and it is frustrating. The xp_delete_file SP reports successful completion but does nothing.
|||

I've never been too fond of Maintenance plans, there is only a limited amount of control with them. If I want the delete function for old backup files I use the -DelBkUps switch in the sqlmaint utility. Put this into a multi step SQL Agent job and you have ultimate control.

BOL - ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/937a9932-4aed-464b-b97a-a5acfe6a50de.htm

|||

Change your code from

EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'.bak',N'10/24/2006

to

EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'bak',N'10/24/2006 13:12:32'

That worked for me it seemed that '.BAK' is not accepted and 'BAK' as extension is.

Daan Stam

|||I added the \ after the unc path after I browsed to D:\ Sql Server Backups a folder that I created and the clean up worked for both bak and trn. We are running 2005 sp1, by default the path is incomplete when you click on the folder you want. Thanks for posting the fix|||Why does Microsoft have to keep changing things to make it more difficult? Why didn't they just keep it the same as sql 2000. This is ridiculous!

HOW TO DELETE OLD BACK UP FILES AND TRANSACTION LOGS IN SQL 2005

SQL 2005 DOES NOT HAVE THE SAME FEATURE THAT WAS IN 2000 TO DELETE OLDER FILES OLDER THAN CERTAIN DAYS. I DO HAVE A JOB THAT CREATES A FULL DAILY BACKUPS BUT SINCE I DON'T HAVE ENOUGH SPACE, I WOULD LIKE TO FIND A WAY TO DELETE ALL OLD BACKUP FILES OLDER THAN X AMOUNT OF DAYS AND ALSO DELETE TRANSACTION FILES OLDER THAN X AMOUNT OF DAYS. IS THERE A TOOL TO DO THIS. CAN ANYONE HELP PLEASE.

EMADKB

The Maintenance Cleanup task removes files related to maintenance plans, including database backup files and reports created by maintenance plans.|||

Unfortunately - it does not seem to control deletion of the backup files!

It cleans out information from Datebase logs - but that is not really the problem.

At least from what we see - it does not delte the older files.

We have a cleanup running but older files do not go away.

Bob

|||

It's happening to me too. In our maintanance plans we have clean up tasks set to remove old files but no files are ever deleted. Even running EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'.bak',N'10/24/2006 13:12:32' manually still did not remove any files.

Anyone find a cure for this?

thanks!

|||The maintenance cleanup task will work but you need to specify the folder the files are in and the appropriate extension. SP1 does have an option for "include 1st level folders" in this case it will search the folder you specify and one level down. Also I seem to remember that for the backup files or the logs I needed the / at the end of the folder specification and one of them I didn't. We no longer use maintenance plans for backups and I don't remember which it was. Good Luck.|||There's definately a bug in the Maintanance Plan Clean up task, it will not delete any files. Until they fix it we'll have to do it via a SQL command.|||

There are multiple Microsoft Connect items around Maintenance Plans that we've addressed in SQL Server 2005 Service Pack 2. I am not sure why the Maintenance Plan clean up task is not working for you. In our labs, it is definitely working with pre-release service pack 2 bits.

Add Cleanup Task to Wizard
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126800

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||

We've installed SP1 and SQL 2k5 does not delete the old .bak nor .trn backup files. SP1 gave us the checkbox for sub-folders, but it still won't delete the old backups.

Unfortunately my 60+ production databases aren't setup in your Lab and I don't have pre-release service pack 2 bits, so I need to know what MS is going to do to help people in the real world, like me, delete the old backups?

|||We are also experiencing this problem with our production SQL 2005 SP1 databases and it is frustrating. Thexp_delete_file SP reports successful completion but does nothing.
|||

I've never been too fond of Maintenance plans, there is only a limited amount of control with them. If I want the delete function for old backup files I use the -DelBkUps switch in the sqlmaint utility. Put this into a multi step SQL Agent job and you have ultimate control.

BOL - ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/937a9932-4aed-464b-b97a-a5acfe6a50de.htm

|||

Change your code from

EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'.bak',N'10/24/2006

to

EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'bak',N'10/24/2006 13:12:32'

That worked for me it seemed that '.BAK' is not accepted and 'BAK' as extension is.

Daan Stam

|||I added the \ after the unc path after I browsed to D:\ Sql Server Backups a folder that I created and the clean up worked for both bak and trn. We are running 2005 sp1, by default the path is incomplete when you click on the folder you want. Thanks for posting the fix|||Why does Microsoft have to keep changing things to make it more difficult? Why didn't they just keep it the same as sql 2000. This is ridiculous!

HOW TO DELETE OLD BACK UP FILES AND TRANSACTION LOGS IN SQL 2005

SQL 2005 DOES NOT HAVE THE SAME FEATURE THAT WAS IN 2000 TO DELETE OLDER FILES OLDER THAN CERTAIN DAYS. I DO HAVE A JOB THAT CREATES A FULL DAILY BACKUPS BUT SINCE I DON'T HAVE ENOUGH SPACE, I WOULD LIKE TO FIND A WAY TO DELETE ALL OLD BACKUP FILES OLDER THAN X AMOUNT OF DAYS AND ALSO DELETE TRANSACTION FILES OLDER THAN X AMOUNT OF DAYS. IS THERE A TOOL TO DO THIS. CAN ANYONE HELP PLEASE.

EMADKB

The Maintenance Cleanup task removes files related to maintenance plans, including database backup files and reports created by maintenance plans.|||

Unfortunately - it does not seem to control deletion of the backup files!

It cleans out information from Datebase logs - but that is not really the problem.

At least from what we see - it does not delte the older files.

We have a cleanup running but older files do not go away.

Bob

|||

It's happening to me too. In our maintanance plans we have clean up tasks set to remove old files but no files are ever deleted. Even running EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'.bak',N'10/24/2006 13:12:32' manually still did not remove any files.

Anyone find a cure for this?

thanks!

|||The maintenance cleanup task will work but you need to specify the folder the files are in and the appropriate extension. SP1 does have an option for "include 1st level folders" in this case it will search the folder you specify and one level down. Also I seem to remember that for the backup files or the logs I needed the / at the end of the folder specification and one of them I didn't. We no longer use maintenance plans for backups and I don't remember which it was. Good Luck.|||There's definately a bug in the Maintanance Plan Clean up task, it will not delete any files. Until they fix it we'll have to do it via a SQL command.|||

There are multiple Microsoft Connect items around Maintenance Plans that we've addressed in SQL Server 2005 Service Pack 2. I am not sure why the Maintenance Plan clean up task is not working for you. In our labs, it is definitely working with pre-release service pack 2 bits.

Add Cleanup Task to Wizard
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126800

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||

We've installed SP1 and SQL 2k5 does not delete the old .bak nor .trn backup files. SP1 gave us the checkbox for sub-folders, but it still won't delete the old backups.

Unfortunately my 60+ production databases aren't setup in your Lab and I don't have pre-release service pack 2 bits, so I need to know what MS is going to do to help people in the real world, like me, delete the old backups?

|||We are also experiencing this problem with our production SQL 2005 SP1 databases and it is frustrating. The xp_delete_file SP reports successful completion but does nothing.|||

I've never been too fond of Maintenance plans, there is only a limited amount of control with them. If I want the delete function for old backup files I use the -DelBkUps switch in the sqlmaint utility. Put this into a multi step SQL Agent job and you have ultimate control.

BOL - ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/937a9932-4aed-464b-b97a-a5acfe6a50de.htm

|||

Change your code from

EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'.bak',N'10/24/2006

to

EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'bak',N'10/24/2006 13:12:32'

That worked for me it seemed that '.BAK' is not accepted and 'BAK' as extension is.

Daan Stam

|||I added the \ after the unc path after I browsed to D:\ Sql Server Backups a folder that I created and the clean up worked for both bak and trn. We are running 2005 sp1, by default the path is incomplete when you click on the folder you want. Thanks for posting the fix

HOW TO DELETE OLD BACK UP FILES AND TRANSACTION LOGS IN SQL 2005

SQL 2005 DOES NOT HAVE THE SAME FEATURE THAT WAS IN 2000 TO DELETE OLDER FILES OLDER THAN CERTAIN DAYS. I DO HAVE A JOB THAT CREATES A FULL DAILY BACKUPS BUT SINCE I DON'T HAVE ENOUGH SPACE, I WOULD LIKE TO FIND A WAY TO DELETE ALL OLD BACKUP FILES OLDER THAN X AMOUNT OF DAYS AND ALSO DELETE TRANSACTION FILES OLDER THAN X AMOUNT OF DAYS. IS THERE A TOOL TO DO THIS. CAN ANYONE HELP PLEASE.

EMADKB

The Maintenance Cleanup task removes files related to maintenance plans, including database backup files and reports created by maintenance plans.|||

Unfortunately - it does not seem to control deletion of the backup files!

It cleans out information from Datebase logs - but that is not really the problem.

At least from what we see - it does not delte the older files.

We have a cleanup running but older files do not go away.

Bob

|||

It's happening to me too. In our maintanance plans we have clean up tasks set to remove old files but no files are ever deleted. Even running EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'.bak',N'10/24/2006 13:12:32' manually still did not remove any files.

Anyone find a cure for this?

thanks!

|||The maintenance cleanup task will work but you need to specify the folder the files are in and the appropriate extension. SP1 does have an option for "include 1st level folders" in this case it will search the folder you specify and one level down. Also I seem to remember that for the backup files or the logs I needed the / at the end of the folder specification and one of them I didn't. We no longer use maintenance plans for backups and I don't remember which it was. Good Luck.|||There's definately a bug in the Maintanance Plan Clean up task, it will not delete any files. Until they fix it we'll have to do it via a SQL command.|||

There are multiple Microsoft Connect items around Maintenance Plans that we've addressed in SQL Server 2005 Service Pack 2. I am not sure why the Maintenance Plan clean up task is not working for you. In our labs, it is definitely working with pre-release service pack 2 bits.

Add Cleanup Task to Wizard
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=126800

Paul A. Mestemaker II
Program Manager
Microsoft SQL Server Manageability
http://blogs.msdn.com/sqlrem/

|||

We've installed SP1 and SQL 2k5 does not delete the old .bak nor .trn backup files. SP1 gave us the checkbox for sub-folders, but it still won't delete the old backups.

Unfortunately my 60+ production databases aren't setup in your Lab and I don't have pre-release service pack 2 bits, so I need to know what MS is going to do to help people in the real world, like me, delete the old backups?

|||We are also experiencing this problem with our production SQL 2005 SP1 databases and it is frustrating. The xp_delete_file SP reports successful completion but does nothing.|||

I've never been too fond of Maintenance plans, there is only a limited amount of control with them. If I want the delete function for old backup files I use the -DelBkUps switch in the sqlmaint utility. Put this into a multi step SQL Agent job and you have ultimate control.

BOL - ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/sqlcmpt9/html/937a9932-4aed-464b-b97a-a5acfe6a50de.htm

|||

Change your code from

EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'.bak',N'10/24/2006

to

EXECUTE master.dbo.xp_delete_file 0,N'D:\SQL2005\MSSQL.1\MSSQL\Backup',N'bak',N'10/24/2006 13:12:32'

That worked for me it seemed that '.BAK' is not accepted and 'BAK' as extension is.

Daan Stam

|||I added the \ after the unc path after I browsed to D:\ Sql Server Backups a folder that I created and the clean up worked for both bak and trn. We are running 2005 sp1, by default the path is incomplete when you click on the folder you want. Thanks for posting the fix