Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
DB Engine
SQL ServerMSDESQL Server CE
Services
Analysis (Data Mining)Analysis (OLAP)DTSIntegration ServicesNotification ServicesReporting Services
Programming
CLRConnectivitySQLXML
Other Technologies
ClusteringEnglish QueryFull-Text SearchReplicationService Broker
General
Data WarehousingPerformanceSecuritySetupSQL Server ToolsOther SQL Server Topics
DirectoryUser Groups
Related Topics
MS AccessOther DB ProductsMS Server Products.NET DevelopmentVB DevelopmentJava DevelopmentMore Topics ...

SQL Server Forum / General / Other SQL Server Topics / March 2007

Tip: Looking for answers? Try searching our database.

Allowing users to truncate log file

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
hedgracer - 30 Mar 2007 16:56 GMT
I would like to allow a particular user to truncate a log file in a
stored procedure that the user runs every day. At this moment the only
personnel that can truncate the log file are personnel with sysadmin
rights. Is there any way to do this in sql server 2005 without
granting this user sysadmin rights (something we REALLY don't want to
do)? Thanks for all your help in advance.

Dave C.
Erland Sommarskog - 30 Mar 2007 22:41 GMT
> I would like to allow a particular user to truncate a log file in a
> stored procedure that the user runs every day. At this moment the only
> personnel that can truncate the log file are personnel with sysadmin
> rights. Is there any way to do this in sql server 2005 without
> granting this user sysadmin rights (something we REALLY don't want to
> do)? Thanks for all your help in advance.

Yes, this can be done with help of certificates. I have an article on my
web site that describes this in detail:
http://www.sommarskog.se/grantperm.html.

However, this not at all sound right to me, at least if the user would
truncate the log file every day. Truncating the log is something you
only do in exceptional cases when there is an emergency. Normally, you
either:
1) Run with full recovery and schedule regular full backups as well as
  transaction log backups.
2) Run with simple recovery and schedule only full backups. The log
  will be auto-truncated.

When you run with full recovery, you do so, because you want to be able
to recover the database to any given point in time. But if you truncate
the log, you lose that possibility. Which in fact is self-evident in
SQL 2005, where the only way to do this is to set the database into
simple recovery.
Signature

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.