Applying checkpoint

Question

Applying checkpoint

SQLisAwe5oMe

SSChampion

Points: 14052
More actions
February 28, 2013 at 12:03 pm

#274690

Hi,
Do you need to have access to master in order to apply checkpoint in your query? Please advise. The login I am using has db_owner rights...not mapped to master.....is this good enough to run checkpoint?
SueTons.
Regards,
SQLisAwe5oMe.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

From Books Online:

Permissions
CHECKPOINT permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles, and are not transferable.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 2

GilaMonster (2/28/2013)
From Books Online:
Permissions
CHECKPOINT permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles, and are not transferable.

Thanks Gail, but this particular login is mapped to the db which db_owner rights....however, when query is running with checkpoint getting following error message, “Only the owner of database "master" or someone with relevant permissions can run the CHECKPOINT statement.”

Is this mean, that this login needs to have access to master? and public access is sufficient?

SueTons.

Regards,
SQLisAwe5oMe.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

The login needs to be sysadmin or to have db_owner or backup admin fixed server roles on the database where the checkpoint is run. If you are running checkpoint against the master database, they must have those permissions in master.

Not sure why you'd run checkpoint in master anyway.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLisAwe5oMe SSChampion Points: 14052 More actions · Answer 4

GilaMonster (2/28/2013)
The login needs to be sysadmin or to have db_owner or backup admin fixed server roles on the database where the checkpoint is run. If you are running checkpoint against the master database, they must have those permissions in master.
Not sure why you'd run checkpoint in master anyway.

Thanks again Gail.

SueTons.

Regards,
SQLisAwe5oMe.