Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

My problem is that when I try to shrink the Log it works when I am logged in as SA but when I log in as a "normal" USER it does not shrink the log. (SQL Express 2008r2)

I have tried to find a answer to my problem but I cant. So please help me! =)

share|improve this question
    
-1 after comments to my answer –  gbn Feb 1 '12 at 12:22
add comment

migrated from stackoverflow.com Feb 1 '12 at 13:46

This question came from our site for professional and enthusiast programmers.

2 Answers

The last thing you want is allowing a normal user to run DBCC SHRINKxxx

This should be reserved for highly privileged users to run in a few rare circumstances. This should not be a regular or normal operation.

Permissions for both DBCC SHRINKDATABASE and DBCC SHRINKFILE are

Requires membership in the sysadmin fixed server role or the db_owner fixed database role.

If you really want to run this because you know better, then use a stored proc with EXECUTE AS

CREATE PROC dbo.IKnowBetter
WITH EXECUTE AS OWNER -- escalates to db_owner in dbo schema
AS
DBCC SHRINKFILE (1);
DBCC SHRINKFILE (2);
GO
GRANT EXECUTE ON dbo.IKnowBetter TO OikUser;
share|improve this answer
    
It's a backup program that runs the script and it takes the USER info from a .ini-file for the system I want to take the backup on. Those USERS are allready set do db_owners. –  user1065878 Feb 1 '12 at 10:22
    
@user1065878: check they really are db_owner and you're in the correct database. Otherwise, MSDN must wrong and needs corrected via MS Connect... –  gbn Feb 1 '12 at 10:24
    
Yes they really are members of the db_owner role so I will post on MS Connect. Thanks for your help! –  user1065878 Feb 1 '12 at 12:19
    
@user1065878: don't embarrass yourself. I have shrunk files with db_owner only without sysadmin so MSDN is correct. I'd suggest you read the rest of my comment, which states "in the correct database". You are probably trying to shrink the wrong DB files or the user isn't the one you expect. –  gbn Feb 1 '12 at 12:22
    
Hmm okey. I belive I have to do embrass myself because I can't find a solution and I have tested on several systems. Anyway I will continue to try to find a solution.. –  user1065878 Feb 1 '12 at 12:39
show 2 more comments

Regular user have no permissions to do maintenance tasks. If you want the regular user to perform these tasks - add him to fixed server role sysadmin or make him a db_owner of the DB

For details see here: DBCC SHRINKFILE

share|improve this answer
    
Can you show where you got those permissions from? –  gbn Feb 1 '12 at 10:02
    
@gbn Oops! Fixed. –  Oleg Dok Feb 1 '12 at 10:04
    
The User is allready set to db_owner. If I do the same on a 2005 express i does work –  user1065878 Feb 1 '12 at 10:07
    
@user1065878: in the correct database? db_owner is not a normal user –  gbn Feb 1 '12 at 10:10
    
Hmm I maby used the wrong word "normal". The thing is that it's a program running the script. And it takes the SQL user information from a .ini-file and the user in there is set to a db_owner. Maybe it doesent make sense but that is how it works right now =). –  user1065878 Feb 1 '12 at 10:18
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.